Re: Amort table - doesn't add up

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob (anonymous_at_discussions.microsoft.com)
Date: 02/17/04 

Date: Tue, 17 Feb 2004 09:11:13 -0800

Hi Norman,
   Care to clarify your last response a little please?
Are you saying that the "true" cost (dollar amount
viewpoint) of the loan with rolled in fees is
>Rolled into loan method:
>=PMT(6%/12,120,100000+1000,0,0)*120-1000
>Returns: -135556.848353282

and paying the fees out of pocket is

>Out of Pocket method:
>=PMT(6%/12,120,100000,0,0)*120-1000
>Returns: -134224.602329982
?

I would be more likely to say the the "true" cost of a
loan would be the total of all payments (here, payments
means principal, interest, PMI and other associated
recurring fees, and any out of pocket set up fees or
closing costs) minus the beginning loan balance. Or put
another way, anything paid by the borrower at any time
before or during the loan, except for principal balance
repayments. Because if you lent me $10, and I paid you
back $10 however long it took me to do that and you
declared my debt paid in full, the true cost of my loan
was $0. The $10 I repaid you wasn't mine to begin with,
so it wasn't a cost to me. If I had to pay you $11 to
have it paid in full, then the "true" cost of the loan to
me would be $1. I'm not talking about note rates, APRs,
effective/nominal rates or things of that nature, because
as I previously mentioned, American banks calculate them
in an arbitrary manner. But nothing is arbitrary about
the actually dollars that a borrower repays. Also, how
would you calculate the "true" cost of your example loan
if it were taken out today, and I wanted to know what the
total "true" cost were 36 months from now? How about if
the closing costs were instead $5000, and say only 4
months had gone by?
Regards,
Bob

>-----Original Message-----
>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.
>

>
>
>
>--
>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
>
>
>.
>

Relevant Pages

  • Re: Blindly Into the Bubble
    ... they were given a mortgage. ... All fees are considered the income of the lender regardless of any costs they are designed to cover. ... With the points and fee free loan, the APR is always the stated interest rate; with the point and fee based loan, the APR is always more than the stated interest rate. ...
    (soc.retirement)
  • Re: Amort table - doesnt add up
    ... outstanding balance of the loan. ... > Are you saying that the "true" cost (dollar amount ... > closing costs) minus the beginning loan balance. ... Because if you lent me $10, and I paid you ...
    (microsoft.public.excel.newusers)
  • Re: Blindly Into the Bubble
    ... they were given a mortgage. ... The APR reflects the effective yield on a loan including ... origination fees and discount points. ... the lender regardless of any costs they are designed to cover. ...
    (soc.retirement)
  • Re: Amort table - doesnt add up
    ... Calculating true costs of loan! ... Looking at up front payments. ... This is because the fees are paid over a 10 year period. ...
    (microsoft.public.excel.newusers)
  • Re: Which mortgage closing costs are ratably deductible?
    ... costs by amortizing them over the entire loan, ... They are just non-interest costs of getting ... I don't see any rules that categorize these fees one ... the IRS goes into such detail about what's required to deduct all at ...
    (misc.taxes)