Re: Amort table - doesn't add up

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Norman Harker (njharker_at_optusnet.com.au)
Date: 02/17/04 

Date: Wed, 18 Feb 2004 04:27:03 +1100

Hi Bob!

By all means deduct the amount of the loan to get the cost net of
principal repayment as opposed to the total of repayments.

What you regard as the cost at future dates depends upon whether or
not you are only looking forwards or whether you are looking both
forwards and backwards.

If looking forwards only, you compare the future payments with the
outstanding balance of the loan. The complicating factor would be
where the set up costs were rolled into the loan. There are different
approaches to handling that but I suppose I'd tend towards a ratio
approach adopting as the ratio the commencing loan / fees ratio. 

-- 
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:1217b01c3f579$0c186880$a501280a@phx.gbl...
> 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: 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: Amort table - doesnt add up
    ... viewpoint) of the loan with rolled in fees is ... closing costs) minus the beginning loan balance. ... Because if you lent me $10, and I paid you ...
    (microsoft.public.excel.newusers)
  • Re: The actual interest rate on a loan
    ... would give me a loan, but it would bepersonal loan wrapped up as a ... I can pay back the loan early. ... interst rate would be if I paid off the loan in a year. ... amount loaned goes down in time. ...
    (uk.finance)
  • Re: Repayment mortgages
    ... The amount by which the monthly payments reduce the outstanding loan ... At a 6% interest rate, applied monthly at 0.5%, you'd have paid off about ... The amount of debt outstanding when M months remain, out of a total N month ...
    (uk.finance)
  • Re: Amort table - doesnt add up
    ... Just looking at just the effective cost of loan issues. ... account of the lender's internal costs. ... Only the last payment is affected. ... minus the so-called pre-paid finance charges, ...
    (microsoft.public.excel.newusers)