PMT

[Pule]

A loan amount of 100 000 to be repayed over 36 months, interest rate
changes(reduces) after 4 moths and 12 days
How do i calculate the repayment for month 5 and the remaining 31 months?

 

[bpeltzer]

Three steps... First use the PMT function to calculate the monthly payment
based on the initial terms: =PMT(5%/12,36,-100000) (if the annual rate is
5%, for example).
Second, use the PV function to calculate the balance when there are 32
months remaining: =PV(5%/12,32,x), where x is the result of the PMT function
Third, use another PMT function to calculate the monthly payment based on
that balance and the new rate: =PMT(4.5%/12,32,y) (if the annual rate is
4.5%, and y is the result of the PV function).
This could all go into a single formula if you prefer. But it would be
harder to see the logic.

 

[Fred Smith]

You do the calculation in three steps:
1. Calculate the initial payment: a1=pmt(i/12,36,100000)
2. Calculate the balance remaining after month 4: a2=fv(i/12,4,a1,100000)
3. Calculate the payment for this balance: a3=pmt(j/12,32,-a2)

Putting it all together you have:
=PMT(j/12,32,FV(i/12,4,PMT(i/12,36,100000),100000))

Some things to be careful of:
1. Ensure you are calculating 36 payments. In your example, you said "the
remaining 31 months", but in fact, after month 4, there are 32 payments
remaining.
2. None of these formulas takes into account the interest rate changing
"after 4 months *and 12 days*". They assume the interest rate changes after
month 4. If you really need the 12 days taken into account, post back. It's
more complicated, and will make only pennies difference in the 2nd payment,
but if it's a requirement, let us know.

Regards,
Fred