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: $ 751.65
Posts: 3655
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: $ 751.65
Posts: 3655
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.)
