Calculate monthly loan payment with the 365/360 method.

Anyone know what the formula is to calculate a monthly loan payment for the 365/360 method (Actual/360), when given the loan amount, rate and loan period?

Details below:

I'm trying to build a commercial loans calculator that uses the 365/360 method. I believe it is also known as Actual/360. So far it has proved to be extremely difficult. I've scoured the internet and all sorts of documentation for a formula to calculate the payment for 365/360, but everything is based on the 360/360 method.

What I am trying to calculate is:
Payment Amount
Balloon Payment Amount
Total Interest

By entering in:
Loan Amount
Interest Rate
# Payments

I already have a working calculator for 360/360, but I have no idea what the formula is to calculate a monthly payment for a 365/360 loan, the interests is compounded differently so the payment is always off.

I have an example of how 365/360 should be calculated from our old system.

Payment Amount: 883.45
Balloon Payment Amount: 45318.13
Total Interest: 84339.13

The key is to calculate the loan payment, the rest can be calculated using it.
Any help is GREATLY appreciated.

Edit: changed 120,000 to 119,979.17.

Last edited by Vanhail on Mon Jul 13, 2009 5:25 pm; edited 2 times in total

Fri Jul 10, 2009 7:02 pm

coaster Senior Advisor

Cash: $ 1626.30

Posts: 7990
Joined: 11 Oct 2005
Location: Wisconsin

I don't know if this is at all helpful, but I think, based on the description in the help, that Excel's COUPNUM function does what you want.

~Tim~

Sat Jul 11, 2009 4:11 pm

oldguy Senior Member

Cash: $ 749.25

Posts: 3643
Joined: 21 May 2006
Location: arizona

Van - if you multiply 365/360 x 6.25% = 6.337% , you will get your $883/m answer, as opposed to the $877/m answer of a normal amortization.

Not sure what you mean by the $45,318 balloon, or remaining balance? But that would be the balloon if you wanted a 15 year pay-off?

Sat Jul 11, 2009 6:57 pm

Vanhail New Poster

Cash: $ 0.45

Posts: 2
Joined: 10 Jul 2009

quote:Originally posted by oldguy Van - if you multiply 365/360 x 6.25% = 6.337% , you will get your $883/m answer, as opposed to the $877/m answer of a normal amortization.

Not sure what you mean by the $45,318 balloon, or remaining balance? But that would be the balloon if you wanted a 15 year pay-off?

Thanks for the replies... This seems like it should work, but it doesn't quite do it for most of the examples. I forgot a detail =\ There is a potion of prepaid interest, this is paid up front so I believe the financed amount is 119,979.17. I believe this is used in the formula and not the full 120,000.

[image with link removed]

I actually have a working calculator with this formula. The problem is I do not know the formula for the Actual/365 method. AKA 365/360.

Mon Jul 13, 2009 5:28 pm

oldguy Senior Member

Cash: $ 749.25

Posts: 3643
Joined: 21 May 2006
Location: arizona

Your eqn is

MP = (r + r / ((1 + r ) ^N - 1) x P

Try this -

MP = r / (1 - (1 + r )^-N ) x P

I tried to get your eqn to algebraically equal the textbook eqn - the first 'r' term in your eqn seems to be out of place. (Either that or my algebra is lacking.)