Excel RATE set up question

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
Post Reply
Topic Author
Kelly
Posts: 498
Joined: Sun Nov 18, 2012 6:39 am

Excel RATE set up question

Post by Kelly »

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
moshe
Posts: 565
Joined: Thu Dec 12, 2013 12:18 pm
Location: Boston, MA

Re: Excel RATE set up question

Post by moshe »

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
My money has no emotions. ~Moshe | | I'm the world's greatest expert on my own opinion. ~Bruce Williams
Topic Author
Kelly
Posts: 498
Joined: Sun Nov 18, 2012 6:39 am

Re: Excel RATE set up question

Post by Kelly »

Yup, that's it!

Many thanks

Kelly
moshe
Posts: 565
Joined: Thu Dec 12, 2013 12:18 pm
Location: Boston, MA

Re: Excel RATE set up question

Post by moshe »

Hope you get an A :-)

All the best,
~Moshe
My money has no emotions. ~Moshe | | I'm the world's greatest expert on my own opinion. ~Bruce Williams
User avatar
Oicuryy
Posts: 1959
Joined: Thu Feb 22, 2007 9:29 pm

Re: Excel RATE set up question

Post by Oicuryy »

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
Money is fungible | Abbreviations and Acronyms
Post Reply