3

I'm trying to reproduce the schedule, but I cant seem to get the same Payment and Interest for each month. The tricky part here is that the Loan start date is 9 Feb 2017 but the first payment is only due on the 31st of March, from which the schedule then begins. So there is a broken interest from 9 Feb to 28 Feb. Which also get amortized across the schedule.

My compounding is also Monthly.

So my questions are.

  1. How do I calculate the broken interest for 9-28 Feb when using monthly compounding?
  2. How do I get to 14,098.74 for the payment amount. Currently I have the payment amount as 14,029.36, but I'm guessing I have to redistribute the broken interest between my interests over each month which will push it up?

When calculating it with https://financial-calculators.com/ultimate-financial-calculator# I get the schedule as seen below. But I can't seem to reproduce it on my own in excel or C#.

enter image description here

Bracher
  • 133
  • 1
  • 5

2 Answers2

5

I calculated 14098.64 and 14098.74. Here are the methods.

First, what I would say is more mathematically correct.

For a loan with equal payment periods we have the standard formula below.

pv = present value of principal
c = periodic repayment amount
r = periodic interest rate
n = number of periods

enter image description here

With an extended first period the formula is changed like so.

enter image description here

The extension x is 19 days of an average month.

x = 19/(365/12)
pv = 160000
n = 12
r = 0.095/12

Rearranging the extended loan formula for c.

pv = (c (1 + r)^(-n - x) (-1 + (1 + r)^n))/r

∴ c = (pv r (1 + r)^(n + x))/(-1 + (1 + r)^n)

∴ c =  14098.64

Second method

Calculate the extended first period interest by this method, described here.

i1 = pv r + pv (1 + r) (0.095/365) 19 = 2064.16

Note this is incorrectly using the nominal rate compounded monthly as a nominal rate compounded daily, and then not even using compounding. I think the calculation of the extended first period interest should be 2062.98 which, if used in place of i1, results in the repayment calculated above.

dailyrate = (1 + 0.095/12)^(12/365) - 1 = 0.000259283
pv (1 + dailyrate)^(19 + 365/12) - pv = 2062.98

Nevertheless, continuing with i1, add it to the principal and calculate the loan with repayments starting immediately, not waiting a month.

s = pv + i1 = 162064.16

enter image description here

s = (c (1 + r - (1 + r)^(1 - n)))/r

∴ c = (r (1 + r)^(-1 + n) s)/(-1 + (1 + r)^n)

∴ c = 14098.74
Community
  • 1
Chris Degnen
  • 10,107
  • 1
  • 21
  • 36
3

For the first month, it is for additional interest of Feb. For 19 days divided by 365 we get int of 791.23. Add this to Principal of 160,000. On this principal multiply by rate and divide by 12.

The Interest from second month onwards is 30/360. i.e outstanding balance Multiplied by Rate divided by 12.

It is difficult to directly find the EMI in such cases. The simplest way is to put this in spread sheet and use the Excel Goal->Seek function.

Dheer
  • 57,348
  • 18
  • 89
  • 170