I was sitting in my Finance class last Saturday when it occurred to me that Excel is useful for a lot of things – not just data analysis and dashboard presentations. Some of it is fairly simple, just not widely used. As part of my teach to learn initiative, I am going to show a few things I picked up.
Today, we are going learn about financial functions dealing with present value and future value.
Let’s assume that you have $300 extra a month. What should you do with this money, you asked yourself. After you checked with your long term financial goals, you decided this money will be used toward the purchase of a new car. Hmmm.. ..before you approach a car sales man, let’s take a look at how much can you afford – the maximum car value, how much can your $300 a month do?
Don’t ever EVER tell the sales man that you have $300 to spare a month. Do your homework first. Let’s use Excel to figure out the value of the car you can afford.
Open up Excel, and set up something like this:
Basically, we’re filling out 3 out of the 5 values above. I blackened “pv” because we are not interested in that for this case. We know (or can estimate) the interest rate (www.bankrate.com), the number of pay periods, and we know we can afford $300 a month.
I have 3 asterisks above. Let me explain what they are.
*interest rate – Most interest rate are quoted on an annual basis. To get to a monthly level, you need to translate this “annual” interest rate to month. Annual means year. There are 12 months in a year. With the example above, I used an interest rate of 2.9%. 2.9% divided 12 equals 0.24%.
** number of payments – you have to be consistent so because you are telling the formula that you’re paying $300 A MONTH, again, you need to do some translation. Most car loans are done in 3, 4, or 5 years. I’m using 5 years in my example above. All I did was =
5 x 12 = 60 months
*** pmt – this one is easy. We already established that we’re paying $300 a MONTH.
I should also mention that in most cases, the toughest part of the formula is determining what is it that we’re trying to derive. Is it the present value or future value of an item? In this case, it is the future value that we’re trying to figure out. We want to know the future value of $300 a month over a period of 5 years and at an interest rate of 2.9%,
So, the formula to calculate the future value, at 2.9% annual interest rate in 60 months (5 years) with a payment of $300 a month is?
Write this formula in a cell: =FV(0.24%,60,-300)
The answer should be $19,335.61. So, this is how much your car budget is.
In many cases, you want to run a scenario model by changing the values. In that case, you would want Excel to dynamically change the answer as soon as you change the values. So, instead of writing this formula =FV(0.24%,60,-300) write =FV(C2,C3,-C4)
If you look at the picture I have, C2=0.24%, C3=60, and C4=300 so -C4=-300.
Now try changing the values from 300 to 320. The answer would change to 20,635. So, if you had $20 more to spare, your car budget is now increased to $20,635. Does that make sense?
I’m not showing you the present value formula here because I want you to learn it on your own.
Good luck –