Every time value formula I have introduced contains this expression or one very much like it:
Suppose the interest or discount rate “i” is 5% and the compounding periods “n”, equals 8. The above general expression would be converted to this specific expression:
Just a few decades ago before the advent of powerful handheld calculators and Excel spreadsheets evaluating an expression such as this would be a bit time consuming. Of course, you would not have attempted to multiply 1.05 times itself eight times. Either you would have used log and anti-log tables or a slide rule. (If you ever had to use these methods you would have an even greater appreciation for the miracle of present-day calculators and spreadsheets.)
But these new more powerful tools have their drawbacks. Excel and calculators do not just evaluate this one component of time value formulas they evaluate the entire formula. Why would this be a problem?
Calculators and spreadsheet formulas perform time value computations utilizing graphical interfaces in which you have to enter each parameter in a different cell. If you do not provide the correct parameter or the parameter in the correct format you could easily get a wrong answer and not realize it. It’s the old garbage in garbage out problem. Sometimes the correct format of the parameter is not so obvious. A second issue with Excel is that it offers different functions that have similar names. And finally, the spreadsheets and calculators do not give you an obvious way to validate the accuracy of your results.
For example, Excel offers you two very similar sounding functions PV, for present value and NPV, for net present value. The PV function can only be used if the future payments are constant or you are trying to figure out the present value of one lump sum in the future.
Example: A friend wants to start a new business. She needs a $10,000 loan from you. Her first offer is a zero-coupon bond arrangement paying you $10,000 in five years. Suppose you demand a 5% return. How much cash would you be willing to give her now? This is a present value computation. You choose the PV formula in Excel. Here is what it looks like.
If you look closely at the Excel PV GUI above you can see that the answer given is $7,835.26 and once you hit the OK button this result will show up in cell B5.
How do you know the answer is right? Well as a check you can actually enter the basic formula in a cell below like this and test to see your result. $7,835.26.
The cell adjacent to the row labeled verification check contains the PV value formula we have seen and used before:
OK, let’s say your pal really needs more than $7,835 up front. You still want to get your 5% and you do not want the entire deal to involve more than $10,000 in future or present dollars. Your pal knows that she cannot afford to make much in the way of installment payments in the first few years but perhaps they could afford five $2,000 payments at the end of each period. To make your 5% what amount of cash are you prepared to lend under these terms? Well, the Excel PV formula works in this case as well.
As you can see Excel indicates that you would discount your original loan to $8,658.95.
Now how do we check this using original formulas? We could do it on a spreadsheet like this.
We index the annual payments in the first column and place the annual payment in the next column and then put in our basic formula in the last row. Copy down and sum and our initial calculation of $8,659 is confirmed.
Now suppose we are not done negotiating yet. Our pal is not sure that she can make $2,000 payments at the end of the first few years. She would feel more comfortable making zero payments for the first two years and a payment of $2,000 at the end of year three, $3,000 at the end of year four and $5,000 at the end of year five. Now again given that you want to earn 5% what would you be willing to lend her under this repayment plan? Now the Excel function PV will not work for this scenario because the repayment stream is not constant. Here we have to invoke Excel’s NPV function instead. The function and input and result look like this.
Now to recheck the results you can reenter the basic formula like this.
But note that you had to make the cell reference to the interest rate fixed. Here you see the result match and you would be willing to front your pal $8,113.
Your friend decides she really needs the full $10,000 now and so offers another suggestion. How about a fully amortized loan with affordable monthly principal and interest payments? In order to make it more affordable in the first lean years of her venture she proposes a seven-year payback period instead of a five-year payback. You counter that due to the extended payback period you want the interest rate increased to 6%. Excel has the precise function, PMT, to easily compute the monthly principal and interest payment. So, you set up the worksheet and formula like this.
Now you blithely enter the cell references as above and click ok and you get a monthly payment of $1,791.35.
This looks very good to you but your pal has a seizure and says “WTF that number can’t be right. Over seven years I am going to be paying you about $150,000 on an initial $10,000 loan. I don’t think so.”
So, what went wrong here? You go back to your spreadsheet and you realize that you put seven years in for the Nper[b1] input and you should have put in 7 x 12, or 84, periods in this input. Easily fixed. This is an example of getting the wrong parameter value in an Excel function interface.
First you modify your spread sheet so the actual number of months shows as a separate cell input. So now your input and formula look like this.
This time you have entered your new cell C5 which represents the actual number of months of payments, eighty-four. You hit ok and now derive a monthly payment of only $604.53. This looks better. You happily present this new figure to your pal. She looks at it says WTF again. “Do the math friend if I pay you a little over $600 a month for 74 months I will end up paying over $44,000 on a $10,000 loan. You told me that you would be happy to collect 6% on the loan balance. The monthly figure you are quoting me implies an interest rate I would have to pay if I get the loan from Vinny the “Vig Legbreaker”. Go back and get the right figure.”
So, you go back to your Excel spreadsheet and you realize that you were supposed to enter into the PMT interface the interest rate per month, not the annual interest rate. You create a separate cell for interest per payment period and enter this into the formula input and you now get a monthly payment of about $146 per month.
A lot more reasonable figure than the previous incorrectly computed monthly payments. But since you have embarrassed yourself twice in inputting the incorrect amounts into the Excel formula is there a way to independently verify that you finally have the right figure?
Well yes you can. Recall that the formula for the fixed payment loans looks like this.
Admittedly an ugly looking thing but you can easily plug this into your spreadsheet and independently confirm that your formula is right. And as you can see the results are identical.
Now you may think that these extra efforts are simply redundant and a waste of time. And yes, these independent error checks are redundant and they take more time but as I hope this last example shows it is very easy to input incorrect numbers into Excel formulas. So always take the time to independently check your results.
Copyright 2018 Michael Sack Elmaleh