PMT formula help

[Guest]

I need a function(formula) that will amortize a loan based on a 365/360
(actual/360) basis, no compounding, with payment due at the end of each
payment period (ie monthly payments with note dated 11/6, first payment 12/6)
So with a loan amount of $1,662,000 at 7.5% 10year amortization the loan
payment should be $19,821.32 with this formula (On our current formula we
come up with $19,728.24)

This is what I am currently using but it is not a true 30/360 formula
=ROUND(-(PMT(C11/12,C12,C10)-0.005),2) where C11= Annual IR, C12= Term in
months and C10=Original loan amount and is not accurate for what I need. Can
anyone help?

 

[Niek Otten]

<payment should be $19,821.32 >

How did you get that?

I'm not sure the 365/360 matters. If you do not pay monthly but every period (however defined), than the formula is correct, as
you can easily check by setting up an amortization table.
But maybe you want to tweak the interest%. Question is How?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I need a function(formula) that will amortize a loan based on a 365/360
| (actual/360) basis, no compounding, with payment due at the end of each
| payment period (ie monthly payments with note dated 11/6, first payment 12/6)
| So with a loan amount of $1,662,000 at 7.5% 10year amortization the loan
| payment should be $19,821.32 with this formula (On our current formula we
| come up with $19,728.24)
|
| This is what I am currently using but it is not a true 30/360 formula
| =ROUND(-(PMT(C11/12,C12,C10)-0.005),2) where C11= Annual IR, C12= Term in
| months and C10=Original loan amount and is not accurate for what I need. Can
| anyone help?
|

 

[joeu2004]

I'm not sure the 365/360 matters.
[....]
But maybe you want to tweak the interest%. Question is How?

Perhaps this pointer will help to put the discussion on track:
http://www.efanniemae.com/mf/guidesforms/doc/lendermemos/LM01_15.doc .

Unfortunately, the document is not self-explanatory, IMHO. But this
statement might be helpful:

"Calculation of the monthly principal and interest (P&I) payment for a
loan utilizing the Actual/360 option is the same as for a loan using
the standard 30/360 option. The difference between the Actual/360 and
30/360 methods is that the amount of each monthly payment that is
allocated to interest will be based on the actual number of calendar
days during such month. In a 31-day month, more of the monthly payment
amount will be allocated toward interest than toward principal under
the Actual/360 method as compared to 30/360 method. The amount
allocated to interest for each month will vary depending on the actual
number of calendar days during such month. As a result, since there
are actually 365/366 days in a year, loans using an Actual/360 payment
schedule amortize more slowly and generate more interest than a loan at
the same note rate using a 30/360 payment."

 

[Guest]

I need a function(formula) that will amortize a loan based on a 365/360
(actual/360) basis, no compounding, with payment due at the end of each
payment period (ie monthly payments with note dated 11/6, first payment 12/6)
So with a loan amount of $1,662,000 at 7.5% 10year amortization the loan
payment should be $19,821.32 with this formula

Sorry, but I cannot provide a formula (yet). In fact, I suspect that no
formula will suffice, unless you write a VBA function. (Note: Perhaps
Excel's goal-seek feature would do the trick. I am not sufficiently familiar
with it to try.)

But I can confirm that $19,821.32 is the smallest payment that reduces the
loan to zero based on the above terms and my interpretation of the actual/360
method described in
http://www.efanniemae.com/mf/guidesforms/doc/lendermemos/LM01_15.doc .

(On our current formula we come up with $19,728.24)
This is what I am currently using but it is not a true 30/360 formula
=ROUND(-(PMT(C11/12,C12,C10)-0.005),2)

I would compute that more simply as follows:

=roundup(pmt(C11/12, C12, -C10), 2)

where C11 is 7.5%, C12 is 10*12, and C10 is 1,662,000.

It is debatable whether to use ROUNDUP() or ROUND(). But in either case,
that payment works if we assume that the interest per month is computed using
the constant monthly interest rate of 7.5%/12. (I presume that is the
so-called 30/360 method.)

But I presume that the actual/360 method computes the interest per month as
(D2-D1)*7.5%/360, where D1 and D2 are the previous and current payment dates
respectively.

Indeed, when I set up a 120-month amortization schedule based on that
assumption -- using 11/6/2006 as the loan date, 12/6/2006 as the first
payment date, and the 6th of the month for each successive payment -- the
loan is reduced to zero, with the last payment amounting to $19,821.06.

If you are interested, I could explain a paradigm for setting up the
amortization schedule and doing a "goal seek" algorithm manually. The
algorithm could be implemented using VBA -- but someone else would have to
provide that solution.

HTH.

 

[Guest]

Thanks, I will tweak it some more. I have a complete amortization schedule
setup.

 

[Guest]

It matters in the fact that when you get to larger loan amount the monthly
repayment can have as much as a hundred dollar difference in payment. You
can not just adjust the interest rate. But it does work when you compute the
interest compounding daily payable at the end of each month. Thanks for
your input.