Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mtge calculation (Dly compound interest and multiple interest rate
All,
I'm based in the UK and I'm trying to get to the bottom of a dilemma that I'm facing - "How do I calculate how much the monthly repayment will be for a loan that features two interest rates?" I know the following: Principal - £50,000 Interest compounded - Daily (probably 360 days) Interest rate - 5% for 10 years Reverting to - 7% for 5 years Total mortgage duration - 15 years I've already tried the following function "PMT((1+5%/360)^(360/12)-1,15*12,-50000)", but this only allows for one interest rate and doesn't account for the rate change, later in the term. Any pointers??? Many thx. S |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mtge calculation (Dly compound interest and multiple interest rate
Hi,
I am not completely sure of bellow, maybe someone else can correct me if wrong: When you pay a mortgage, a portion of the payment pays for the principal, the rest is for the interest; these 2 portions are given by PPMT and IPMT PMT(...) = PPMT(...) + IPMT(...) (check online help for these functions) and the vary at each payment period. By summing PPMT over the 10yrs of payment, you should get the overall portion of the Principal you have paid, therefore "Principal - Sum(PPMT)" gives you what is left to pay , which should be the amount on which you have to apply the new rate for 5 years (equivalent to borrow this amount at new rate for 5 yrs). I hope it puts you a bit closer to the right track. -- Regards, Sébastien <http://www.ondemandanalysis.com "Spudson" wrote: All, I'm based in the UK and I'm trying to get to the bottom of a dilemma that I'm facing - "How do I calculate how much the monthly repayment will be for a loan that features two interest rates?" I know the following: Principal - £50,000 Interest compounded - Daily (probably 360 days) Interest rate - 5% for 10 years Reverting to - 7% for 5 years Total mortgage duration - 15 years I've already tried the following function "PMT((1+5%/360)^(360/12)-1,15*12,-50000)", but this only allows for one interest rate and doesn't account for the rate change, later in the term. Any pointers??? Many thx. S |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mtge calculation (Dly compound interest and multiple interest
Thx Sebastien.
When I use this function, I cannot seem to get it to work effectively for the capitalisation of daily interest. It likes annual interest - but throws a wobblie when I incorporate the daily calculation. Any other suggestions?? S "sebastienm" wrote: Hi, I am not completely sure of bellow, maybe someone else can correct me if wrong: When you pay a mortgage, a portion of the payment pays for the principal, the rest is for the interest; these 2 portions are given by PPMT and IPMT PMT(...) = PPMT(...) + IPMT(...) (check online help for these functions) and the vary at each payment period. By summing PPMT over the 10yrs of payment, you should get the overall portion of the Principal you have paid, therefore "Principal - Sum(PPMT)" gives you what is left to pay , which should be the amount on which you have to apply the new rate for 5 years (equivalent to borrow this amount at new rate for 5 yrs). I hope it puts you a bit closer to the right track. -- Regards, Sébastien <http://www.ondemandanalysis.com "Spudson" wrote: All, I'm based in the UK and I'm trying to get to the bottom of a dilemma that I'm facing - "How do I calculate how much the monthly repayment will be for a loan that features two interest rates?" I know the following: Principal - £50,000 Interest compounded - Daily (probably 360 days) Interest rate - 5% for 10 years Reverting to - 7% for 5 years Total mortgage duration - 15 years I've already tried the following function "PMT((1+5%/360)^(360/12)-1,15*12,-50000)", but this only allows for one interest rate and doesn't account for the rate change, later in the term. Any pointers??? Many thx. S |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compound Interest Rate or Growth Rate | Excel Worksheet Functions | |||
How can I determine what the compound interest rate is | Excel Worksheet Functions | |||
Compound interest rate of return on investments | Excel Worksheet Functions | |||
Compound interest calculation | Excel Worksheet Functions | |||
Compound interest calculation | Excel Discussion (Misc queries) |