Alternative to PMT using 360 day basis?

[Ludovico]

I'm trying to build a very detailed amortization schedule worksheet. Most loans in my institution are based on an actual/360 interest basis; the PMT function seems to be based on actual/365. Anybody know of a fix. I want many users across our network to be able to use the spreadsheet, so a solution invloving an add-in to each users's pc will probably not be practical.
Thanks,
Ludovico

 

[Scott R]

...the PMT function seems to be based on actual/365.

Not sure what you mean; the PMT function is N periods. Can you give an example?

 

[Ludovico]

Sure.
What I mean is that if I use PMT to get the monthly payment on a $5,000,000 12 month loan at 7% (Rate =.07/12, NPER = 12, PV = 5000000), the function tells me the monthly payment will be $432,633.73. If I do an amortization table using that payment and accruing interest on an actual/365 day basis, the final payment necessary to payoff the loan in month 12 is very close to the payment calculated by PMT. If I accrue interest using actual/360 (which most banks use) the final payment is off by quite a bit. I was hoping to be able to set up my amortization table to be able to select between the two.
Hope this helps.
Ludovico.

 

[Scott R]

Is 360 the same as 11.836 periods (360/365 * 12)?

In that case, PMT returns $432,858.
Monthly interest would be Balance * .07/11.836.