I can't figure out how to set up this TMV question.
The case facts are that a $100,000 liability grows at 3% per years for 5 years and is then paid off with a 5% loan over the ensuing 5 years. What's the weighted interest rate over the course of the ten years?
I've made several attempts with excel RATE, IRR, PMT, NPER but I can't get an answer that seems correct.
Any help is appreciated!
Kelly
Excel RATE set up question
Re: Excel RATE set up question
Kelly,
After 5 years a $100,000 liability will grow at 3% to $115,927.41 which represents 100,000 in principle and $15,927.41 in interest.
$15,927.41=FV(3%,5,0,-100000).
If you then take a loan at 5% to pay the liability(now $115,927.41) you will pay $26,776.31 per payment which represents $133,881.55 in total payments when you retire the original liability. ($26,776.31 * 5)
$26,776.31=PMT(5%,5,-115927.41)
You paid $33,881.55 in interest over the ten years to retire the original $100,000 liability. your average weighted interest rate is (33881.55/100000)/10. = 3.39%
Does this help?
~Moshe
After 5 years a $100,000 liability will grow at 3% to $115,927.41 which represents 100,000 in principle and $15,927.41 in interest.
$15,927.41=FV(3%,5,0,-100000).
If you then take a loan at 5% to pay the liability(now $115,927.41) you will pay $26,776.31 per payment which represents $133,881.55 in total payments when you retire the original liability. ($26,776.31 * 5)
$26,776.31=PMT(5%,5,-115927.41)
You paid $33,881.55 in interest over the ten years to retire the original $100,000 liability. your average weighted interest rate is (33881.55/100000)/10. = 3.39%
Does this help?
~Moshe
My money has no emotions. ~Moshe |
|
I'm the world's greatest expert on my own opinion. ~Bruce Williams
Re: Excel RATE set up question
Yup, that's it!
Many thanks
Kelly
Many thanks
Kelly
Re: Excel RATE set up question
Hope you get an A
All the best,
~Moshe
All the best,
~Moshe
My money has no emotions. ~Moshe |
|
I'm the world's greatest expert on my own opinion. ~Bruce Williams
Re: Excel RATE set up question
Here is another way to calculate a rate using moshe's amount for the payments.
3.73% =IRR({-100000,0,0,0,0,0,26776.31,26776.31,26776.31,26776.31,26776.31})
Ron
3.73% =IRR({-100000,0,0,0,0,0,26776.31,26776.31,26776.31,26776.31,26776.31})
Ron
Money is fungible |
Abbreviations and Acronyms