![]() ![]() You want to calculate the reduced amount borrowed before you do the multiplication. Note that the parentheses are very important here. Since the price is in Cell B2, I can replace B2 in my formula with (B2-1000). This scenario is reducing the price of the car by 1,000. So I go back to Cell C10, click on it, and edit the formula. But I need to adjust for my scenarios, which I have not done yet. It is the monthly payment if you borrow 19,000 at 8% annual interest for 48 months and it comes to 463.85. So now I have the same number in all three of these cells. Then go to Cell C110, click on it, and Paste. ![]() So I click on cell C7, then go to the formula bar, highlight the formula, and then Copy. 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. But note that I haven’t actually put in the scenario adjustments yet. This is really the same thing as formula above. In Cell C7, opposite the label “Base Case” I put in my formula, replacing the variable names in the above formula with cell addresses. In Cell A14 I typed “.005 reduction in rate” (Note that I divided by 100 to get the decimal equivalent of half a percent) The variable I left out is the monthly payment amount, and that is what I would solve for. This is my raw material for my calculations. In Cell A5 I Type “Monthly Interest Rate” Then because the interest rate is given as an annual rate, I do an intermediate calculation This represents a 4 year loan, with 48 monthly payments. None of this is strictly necessary, but making the spreadsheet more attractive and a little easier to read is not a bad thing. Finally, I right-click, select Format Cells, Background, and select a nice blue background. I type in Manual Model, make it bold, and increase the font size to Arial 12. This is just to the right of the Left Align, Center, Right Align, and Justify buttons. I set up one of these on a sample spreadsheet like this:įirst, I select three cells A1 through C1, and click the Merge and Center Cells button on the Formatting bar. You can put this into a spreadsheet and do a quick calculation. 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%. Which is the better offer? Well, let’s put in some actual numbers. 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. 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. And I am going to leave it at that, because this is a tutorial on Calc, not on Finance. 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. So the last adjustment is to divide by 12 to get 12 monthly payments. But you don’t pay interest once a year, you pay it every month in your payments. To use this in a calculation you need to convert the percentage into a decimal, and that means dividing by 100 to get. Interest is usually given as an annual percentage, such as 8% per annum. But you cannot simply divide P by N to get your monthly payment because you also need to pay interest on the 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. N = number of months of amortization, determined by length in years of loan J = monthly interest annual interest divided by 100, then divided by 12 ![]() Here is an example (courtesy of, which offers its material under a Creative Commons license), using loan payments: 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. But if you take the time to understand what these functions are doing you can often use them in other ways.Īs 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 thing you need to bear in mind about these is that they are all oriented to doing financial analysis on investments. So it should not come as great surprise that there are many great financial functions in Calc. And the reason for that was that you could do sophisticated financial analysis with spreadsheets. 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |