As we discussed back when we first looked at spreadsheets, they were the killer app that lead to wide-spread adoption of PCs in companies. And the reason for that was that you could do sophisticated financial analysis with spreadsheets. So it should not come as great surprise that there are many great financial functions in Calc. The thing you need to bear in mind about these is that they are all oriented to doing financial analysis on investments. But if you take the time to understand what these functions are doing you can often use them in other ways.
As we pointed out, all functions in Calc take arguments and in this case as you might expect the arguments would have to do with interest rates, principle amounts, time periods, and payment amounts. The general form of such a function ties all of these together in a relationship that lets you specify all but one of them and solve for the remaining one. Here is an example (courtesy ofWikiHow.com, which offers its material under a Creative Commons license), using loan payments:
M = P * ( J / (1 – (1 + J)^ -N))
Where:
M = Monthly payment
P = Principal amount of the loan
J = monthly interest; annual interest divided by 100, then divided by 12
N = number of months of amortization, determined by length in years of loan
So what is going here? Basically, you borrow some amount P, which you need to repay, and you will make payments for N number of months. But you cannot simply divide P by N to get your monthly payment because you also need to pay interest on the loan. Interest is usually given as an annual percentage, such as 8% per annum. To use this in a calculation you need to convert the percentage into a decimal, and that means dividing by 100 to get .08. But you don’t pay interest once a year, you pay it every month in your payments. So the last adjustment is to divide by 12 to get 12 monthly payments. Now, you need to note that this may not precisely match how your bank calculates it, depending on how they compound things, but it should be pretty close for comparison purposes. And I am going to leave it at that, because this is a tutorial on Calc, not on Finance.
Once you have this formula you can do a simple model in a spreadsheet where if you plug in any three of the variables you can calculate the fourth one. For example, suppose the car dealer makes you an offer: You can either get $1,000 off on the price of the car, or get .5% lower interest rate. Which is the better offer? Well, let’s put in some actual numbers. Suppose the car you are looking at normally sells for $19,000, you are looking at a 4-year loan, and the normal interest rate on these loans is 8%. You can put this into a spreadsheet and do a quick calculation. I set up one of these on a sample spreadsheet like this:
First, I select three cells A1 through C1, and click the Merge and Center Cells button on the Formatting bar. This is just to the right of the Left Align, Center, Right Align, and Justify buttons. This merges the three cells. I type in Manual Model, make it bold, and increase the font size to Arial 12. Finally, I right-click, select Format Cells, Background, and select a nice blue background. None of this is strictly necessary, but making the spreadsheet more attractive and a little easier to read is not a bad thing.
In Cell A2 I typed “Price of Car”
In Cell B2 I typed “19,000”
In Cell A3 I typed “Periods”
In Cell B3 I typed “48”. This represents a 4 year loan, with 48 monthly payments.
In Cell A4 I typed “Interest Rate”
In Cell B4 I typed “.08”
Then because the interest rate is given as an annual rate, I do an intermediate calculation
In Cell A5 I Type “Monthly Interest Rate”
In Cell B5 I type “=B4/12”
This is my raw material for my calculations. The variable I left out is the monthly payment amount, and that is what I would solve for.
In Cell A7 I type “Base Case”
In Cell A9 I typed “Scenario A”
In Cell A10 I typed “1,000 off price”
In Cell A13 I typed “Scenario B”
In Cell A14 I typed “.005 reduction in rate” (Note that I divided by 100 to get the decimal equivalent of half a percent)
Then in column C I will put my answers.
In Cell C2 I typed “Monthly Payment”
Now, to use my math skills. In Cell C7, opposite the label “Base Case” I put in my formula, replacing the variable names in the above formula with cell addresses. So now the formula reads
= B2 * (B5 / (1 – (1 + B5)^ -B3))
This is really the same thing as formula above. But note that I haven’t actually put in the scenario adjustments yet. That is OK, because I will first copy the formula exactly to put into Cell C10 for my first scenario, and in Cell C14 for my second scenario. So I click on cell C7, then go to the formula bar, highlight the formula, and then Copy. Then go to Cell C110, click on it, and Paste. Do the same thing for Cell C14. So now I have the same number in all three of these cells. It is the monthly payment if you borrow 19,000 at 8% annual interest for 48 months and it comes to 463.85. But I need to adjust for my scenarios, which I have not done yet. So I go back to Cell C10, click on it, and edit the formula. This scenario is reducing the price of the car by 1,000. Since the price is in Cell B2, I can replace B2 in my formula with (B2-1000). Note that the parentheses are very important here. You want to calculate the reduced amount borrowed before you do the multiplication. Leave that out and you get a very bad answer indeed. But do it right and you should get 439.43. So in rough terms you knock 24 a month off of the payment. What about the second scenario? This was reducing the annual interest rate by .005. But again I need to get this in Monthly terms, which means dividing by 12. Again, I will do an intermediate calculation to make this easier.
In Cell A15 I type “Monthly Interest Rate”
In Cell B15 I type “=(B4-.005)/12”. Again, make sure you put the parentheses around (B4-.005). You need to do this calculation before you do the division.
Now, in my formula in Cell C14, I make the adjustment by replacing every instance of B5 (the old interest rate number) with B15, my new number.
Now my formula reads:
=B2*(B15/(1-(1+B15)^-B3))
And my new monthly payment appears to be 459.40. So I would be better off taking the 1000 discount instead of the interest rate reduction in this particular case.
Using a Built-In Function
Of course, this was fairly difficult work to do this, particularly if you are not used to doing financial calculations. So wouldn’t it be nice if you could do the same thing with a built-in function? And you can! There is a financial function called PMT that handles this very nicely. But note you need to have gathered the same information we used above for our manual calculation. The PMT function has these arguments:
Rate = what we had above for our interest rate, but should be the annual rate divided by 12, not the annual rate.
NPER = Number of Periods, which in our example is 48
PV = Present Value, which in this case is the amount you borrowed, i.e. 19000
FV = Future Value, which assuming you pay it all of should be zero
Type = A variable that specifies when the payment occurs each month. If you enter zero, or leave it blank, it is assumed to be the end of the month. If you enter 1, it means your payment is at the beginning of the month. The difference is slight in any case.
So, I added another section to my sheet to show what happens if I use the built-in formula. To do this, you begin by clicking on the cell where you want the formula to be calculated, then go to the function key and click it. Select Financial in the Category drop-down, and then scroll down to PMT. Select it, and then click next. This will bring up a window on the right with fields for each of your variables. Now, you could type in numbers, but that is not the way to do it here. We already have most of our numbers on the spreadsheet. So to grab these cell addresses, we click on the field to put our insertion mark there. Then click on the cell that has the number you want, and it will be added to the field.
So, to do a comparison I go to Cells A18 through C18, click the Merge and Center Cells Button, and apply the font styles and background as above. But in this cell I will type “Using the PMT Formula”. Then:
In Cell A20 I type “Base Case”
In Cell A22 I type “Scenario A”
In Cell A24 I type “Scenario B”
Then in Cell C20, I click on the Function wizard, select the PMT function, and fill it out using the numbers we got earlier:
Rate is Cell B5
NPER is Cell B3
PV is Cell B2
FV is left blank, which means it is assumed to be zero, i.e., the loan will be completely paid off.
For Scenario A
Rate is Cell B5
NPER is Cell B3
PV is Cell B2-1000
FV is left blank, which means it is assumed to be zero, i.e., the loan will be completely paid off.
For Scenario B
Rate is Cell B15
NPER is Cell B3
PV is Cell B2
FV is left blank, which means it is assumed to be zero, i.e., the loan will be completely paid off.
And when you do that, what numbers do you get?
Base case = -463.85
Scenario A = -439.43
Scenario B = -459.40
And these are exactly the numbers we got previously, or almost so. The one difference is that they are reported as negative numbers, but that only means they are money being paid out rather than money coming in. Remember, we are using a function used to evaluate investments, and just turning it around.
Lessons Learned
- The formula we used to do the manual calculation is obviously the exact same formula that Calc used to get these monthly payment numbers. I went through this exercise in part to demonstrate that idea, and to give you some sense of what is going on behind the scenes when you use a formula. I probably won’t do it a second time but I thought it was worth doing once.
- The first step in using a formula is to look at the arguments it requires, and make sure you have them ready-to-hand. If you are not sure what each variable really means, do a little Google research. One thing that may help is that in the cases I have investigated the Calc functions and the Excel functions are identical, and you may find it easier to get a good explanation on an Excel help site. If so, use it. You don’t get bonus points for avoiding the best information.
- I did Intermediate Calculation several times in my example. These are not strictly necessary, but they are usually a good idea. I could have made my formulas even more complicated by adding the adjust terms into them directly, but the problem is that your formulas quickly become almost impossible to debug that way. A good Intermediate Calculation lets you move some of that outside of the formula, and you can usually do a quick sanity check to see if the number you get is plausible. For instance, if you divided by 12, did the answer look like 1/12 of what you started with?
source: www.ahuka.com
No comments:
Post a Comment