0

My company (in which I work) offers a home loan of ₹60,00,000 with simple interest. It is written in terms and conditions of this loan that principal shall be repaid in not more than 225 monthly installments, and interest shall be repaid in not more than 75 monthly installments. The simple interest rate is 9% per annum.

I derived a formula for simple interest emi as follows:

Image of formula derivation.

Please consider F=P(1+ni) for simple interest.

Using this formula I sort of made an Excel algorithm:

Excel algorithms

This Excel algorithm for simple interest works great for short term loans, but for a loan like I mentioned above (with 300 EMIs), it breaks. Towards the end of the loan amortization table, the principal amount becomes negative resulting in negative interest. At the end of the loan, balance does become 0. But the problem is, I don't understand negative principal and negative interest terms. Why are they negative and why does balance becomes zero despite these negative terms? Or is something wrong with my method?

Some images of excel amortization table:

Amortization table for short term simple interest loan- Notice the last principal amount is exactly equal to total interest amount.

  1. Terms and formulas for long term loan

  1. In 196th month amortization table breaks

  1. In the end balance goes to 0 but negative principal and interest

Improve this question
2
  • The "75 monthly installments" for interest makes it sound like what I have seen in the US called a "prepaid interest" loan - where the first payments are ALL interest and the remaining payments are all principal. But the number of interest payments (25% of the total # of payments) do not match the interest rate (9%) so it's not clear. Is there any penalty for prepayment? Or do they tell you how much you'll pay back in total (prin + int)?
    – D Stanley
    Commented Dec 27, 2023 at 21:27
  • For one year, the monthly payment is just larger than the total interest (e.g. 6161.14 < 8846.76), otherwise, you would have gone into negatives with one year too. It looks as if you would need to pay the interest at some point (and stop reducing the principal to < 0). That it sums to 0 at month 300 is just math (because a*(b+c) = ab+ac). Nevertheless, I agree with D Stanley, the interest rate doesn't seem to match the timelime (or the total interest), but it might be in the "not more than" phrasing. Does the offer specify the monthly payments or have some sample payment plans?
    – Solarflare
    Commented Dec 29, 2023 at 8:28

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .