Re: Amort table - doesn't add up
From: Norman Harker (njharker_at_optusnet.com.au)
Date: 02/17/04
- Next message: Robert Rosenberg: "Re: Copying down, scroll speed"
- Previous message: support_at_canhelpyou.com: "What Am I Doing Wrong?"
- In reply to: Bob: "Re: Amort table - doesn't add up"
- Next in thread: Bob: "Re: Amort table - doesn't add up"
- Reply: Bob: "Re: Amort table - doesn't add up"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 17 Feb 2004 12:54:25 +1100
Hi Bob!
Calculating true costs of loan! A matter close to my heart. The
essential technique is one of comparing actual payments with the
effective loan.
Looking at up front payments. If they are paid out of pocket. You
calculate the PMT based upon the loan amount. You then calculate the
RATE based upon the loan less up front fees and the previously
calculated PMT.
Looking at payments that are added to the loan. Add the fees to the
loan and calculate the PMT. Then calculate the RATE based upon the
loan without fees and your calculated PMT.
With both alternatives you can nest the first calculation in the
second calculation to get a direct result.
Here's a comparison of loan fees of $1000 on a loan of $100000 over 10
years at 6% nominal compounded monthly:
Out of Pocket method:
=RATE(120,PMT(6%/12,120,100000,0,0),100000-1000,0,0,0)
Returns: 0.518554630373894%
Rolled into loan method:
=RATE(120,PMT(6%/12,120,100000+1000,0,0),100000,0,0,0)
Returns: 0.51836962152902%
You'll see that the "rolled into loan" method has the lower effective
rate. This is because the fees are paid over a 10 year period.
However, the "rolled into loan" method gives you higher total payments
on the loan because you're paying interest on the fees.
Out of Pocket method:
=PMT(6%/12,120,100000,0,0)*120-1000
Returns: -134224.602329982
Rolled into loan method:
=PMT(6%/12,120,100000+1000,0,0)*120-1000
Returns: -135556.848353282
-- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Bob" <anonymous@discussions.microsoft.com> wrote in message news:1187401c3f4c9$48a6f150$a501280a@phx.gbl... > Norman, > I found a solution (cop up solution, but solution none > the less): > > My am schedule already does a pretty good job. I wanted > to see what the amount of principal paid at any given > time in the future would be. What I was trying to do was > SUMIF the payment number <= the given time in the future, > then add the corresponding prinical payments from the > principal column. You'd think that would work, at least I > did, and it was close. A much simpler way, and it works > with any rounding errors, instead of against them, is to > take the current balance, minus the balance of the future > month in question. Works every time (so far). > > Here's another question that you might be able to > enlighten me on. Say you're trying to determine the "true > cost of a loan" at a given time in the future. What I've > got right now expresses this as amount of interest paid > to date + amount of other fees paid (like PMI) to date + > the closing costs. I'm now convinced that this is > not "true" because I think it's only "true" if the > closing costs were actually paid out of pocket. If they > were rolled into the loan, this is the part I'd like your > opinion on, the interest and PMI portions stay the same, > but change the closing costs to MIN(Closing costs, MAX > (Prinipal paid to date minus Closing Costs,0) because if > you haven't actually paid for them with your own money, > it's not yet a cost to you. What I'm trying to avoid is > this: at the time when the loan is completely paid off, > the "true" cost is total interest paid, plus total other > monthly fees paid, plus total out-of-pocket closing > costs. The reason I'm distinguising between out of poctet > closing costs, and those that are rolled into the new > loan, is because if they were all rolled in, the "true" > cost would be the total of all the payments minus the > beginning loan amount (which is the same as the total > principal paid). By rolling in the closing costs you > actually pay for them over time. The question is, how to > you deteremine how they're paid over time? I'm interested > in how you would do it. Additionally, if some of the > closing costs were paid out-of-pocket, and some more were > rolled in, the "true" costs would add the out of pocket > ones directly, and the rolled in ones only equal the the > principal paid to date maxing at rolled in closing cost's > amount. Note: if the loan is paid off, by winning the > lottery, selling the house, or refinancing, before the > prinical payments to date surpasses the amount of rolled > in closing costs, then the Payoff Amount must cover the > additional require principal to date thus, making all of > the closing costs a cost. If you pay it off when the > balance is half of what it started at, the prinical > payments to date have covered all of the closing costs, > so the how would you find the "true" cost of the loan > then? I'm not talking about APR here, just dollar figures. > > Thanks, > Bob
- Next message: Robert Rosenberg: "Re: Copying down, scroll speed"
- Previous message: support_at_canhelpyou.com: "What Am I Doing Wrong?"
- In reply to: Bob: "Re: Amort table - doesn't add up"
- Next in thread: Bob: "Re: Amort table - doesn't add up"
- Reply: Bob: "Re: Amort table - doesn't add up"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
