Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT
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. "Pule" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PMT
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 "Pule" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|