# What is the formula for loan payoff with daily compounded interest and monthly payment?

My goal is to make a graph using this formula for all possible monthly payments.

Let's say...

``````Loans: 21750
Monthly Payment: 220
Daily Interest: 4.0% / 365
``````

What formula would I use to get the total amount of money (or Principal) I'd use to pay off this loan at this rate?

• keyword: "loan amortization" – keshlam Jun 7 '15 at 16:31
• At 4% DAILY that debt more than doubles each month. There is no way to pay it back with given monthly payment. – Ghanima Jun 7 '15 at 16:38
• @Ghanima excuse me but all internet rates in the market are quoted at nominal (annualized) basis. You divide 4% by 365 to get daily. – base64 Jun 7 '15 at 16:39
• The rate appears annual obviously. OP meant compounded daily. – JTP - Apologise to Monica Jun 7 '15 at 16:39
• @Ghanima Thanks for catching that, I did mean 4% annual but compounded daily. – Vongdarakia Jun 7 '15 at 16:46

Since the compounding period and payment period differs (Compounded Daily vs Paid Monthly), you need to find the effective interest rate for one payment period (month). This means that each month you pay 0.33387092772% of the outstanding principal as interest.

Then use this formula to find the number of months: Where PV = 21750, Pmt = 220, i = 0.0033387092772 That gives 120 Months.

Depending on the day count convention, (30/360 or 30.416/365 or Actual/Actual), the answer may differ slightly. Using Financial Calculator gives extremely similar answer.

The total cash paid in the entire course of the loan is 120 x \$220 = \$26,400

• This is perfect! Thanks! Although I am quite curious to why ln is used. Can you explain the meaning of that? – Vongdarakia Jun 7 '15 at 17:04
• @Vongdarakia It is "Natural Log". It is a button on most calculators. ln(y) gives the x in the equation 2.71828^x=y, where 2.71828 is a mathematical constant. – base64 Jun 7 '15 at 17:07
• Ah, I see. Thanks! Also, 26,400 doesn't seem to be the exact amount. Would it make sense to calculate the total amount like this? total = total - (nMonths - wholeMonths) * monthlyPayment? i.e. 26400 - (120.184399 - 120) * 220 = 26359.43. Since the 0.184399 looks to be the overpayment. – Vongdarakia Jun 7 '15 at 17:12
• @Vongdarakia I'd say it is reasonable but 120.18 is just an approximation. Sometimes there are 366 days in a year, sometimes a month is not 30 days, but 31 or 29 or 28. – base64 Jun 7 '15 at 17:38