Alternative to PMT using 360 day basis?

Ludovico

Board Regular
Joined
Apr 3, 2002
Messages
71
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Scott R

Active Member
Joined
Feb 20, 2002
Messages
441
Office Version
  1. 365
Platform
  1. Windows
...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

Board Regular
Joined
Apr 3, 2002
Messages
71
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

Active Member
Joined
Feb 20, 2002
Messages
441
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,144,510
Messages
5,724,782
Members
422,578
Latest member
annsalinas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top