View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
[email protected] joeu2004@hotmail.com is offline
external usenet poster
 
Posts: 418
Default PMT: Daily interest compounding; monthly payment

I wrote:
If you assume 360 days per year -- i.e. 30 days per month -- the following
structure is probably what you are expecting:
=roundup(pmt(fv(7%/360, 30, 0, -1)-1, 12*30, -5000000), 2)


I used 7%/360 and 30 because you wrote "Rate: 7% annual; compounded daily
(360 day year)".

But in a response, you wrote:
Rate/12 compounds the interest MONTHLY
not DAILY. But Rate/365 calculates a DAILY payment,
not a MONTHLY one


(I presume you mean that Rate/365 calculates the daily interest rate -- or
"compounds the interest daily", in your parlance.)

If you want to assume a 365-day year (and perhaps 366 in leap years), the
following is the best closed-form formula that I know of:

=roundup(pmt(fv(7%/365, 365/12, 0, -1)-1, 12*30, -5000000), 2)

Again, you might prefer (1+7%/365)^(365/12) instead of FV(...). And
arguably, you might substitute (3*365+366)/48 for 365/12 -- a slightly better
estimate.

In either case -- 7%/360 or 7%/365 -- the result will not fit reality. (Did
I remember to say "not" this time? ;-) If you create a 360-month annuity
schedule, you will discover a large balloon payment in the last period.

You could tweak the payment upward until the last payment is less than or
equal to the regular monthly payment.