A mortgage calculator
Developing your own spreadsheet
1. Headings a) In Row 8 of the sheet enter these column headings: Month, Sum Owed, Interest, Payment 2. Months column set up a) In Cell A9 Enter March 2000 b) In cell A10 Enter =Edate(A9,1) c) Copy down the formula in A10 until Mar 2025. This should give the framework for calculating 25 year mortgages 3. Basic a) b) c) d) e) f) g) h) i) information and naming cells In Cell A3 Enter “Principal Sum Borrowed” In Cell A4 Enter “Interest Rate” In Cell A5 Enter “Monthly Payment” In Cell D3 Enter 50,000 and format the cell-number format to currency with no decimal places In Cell D4 Enter 0.08 and format the cell number format to percentage with 2 decimal places In Cell D5 Enter 500 and format the cell number format to currency with 2 Decimal places Click on D3 and go to Insert Name Define and give the cell the name “Principal” Similarly, name the interest rate cell as “Interest_rate” Name the monthly payment as “Payment”
4. Calculating the first month a) In cell B9 enter this formula =Principal b) In Cell C9 enter this formula =B9 * Interest_rate/12 c) In cell D9 Enter this formula =Payment d) In cell B10 enter this formula =B9+C9-D9 e) Copy the formulae in C9:D9 down to C10:D10 5. Copying down over the mortgage period a) Copy down the formulae in B10:D10 down for 25 years 6. Draw a line graph of Column A against Column B to see how the mortgage is paid off 7. Formatting the numbers. a) Format the numbers in Column B to be currency with no decimal places b) Format the cells in column C to be currency with 2 decimal places c) Format the cells in Column D to be currency with no decimal places 8. Detecting the pay off time a) Change the formula in B10 to read =IF ((B9+C9-D9) < 0, “Complete”, B9+C9-D9) b) Copy this formula down for the 25 years. c) In Cell D6 Enter this formula =Lookup(“Complete”,B9:B309,A9:A309) d) Format the cell D6 to be number date with a date format which you can choose e) Can you work out what has just happened? 9. For you project folder you should spend some time tidying u the appearance of this spreadsheet and then printing out two versions, one with the data and one showing the formulae (Tools Options View and tick the formulae box) 10. You should be able to use this sheet as a mortgage calculator. By entering different numbers at the top you can check to see how much you need to pay to pay off the loan in a given amount of time. 11. You might like to tidy up this solution by locking all the calculator cells so that it cannot be accidentally altered. You could add comment boxes to explain how to use it. You could use it to generate a quotation letter for clients automatically which would mean recording some macro code and attaching the code to an Active-X control or a menu button.
Page 1 of 1
Created on 02/12/2000 09:18:00