"nicoleradke" wrote:
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/guidesf...os/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.

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

" wrote:

"nicoleradke" wrote:
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/guidesf...os/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.