Computer Applications I :: Projects :: Auto Loan
Problem
You want to buy a new car for $15,000.25 at an interest rate (per year) of 9.6%. You wish to pay off this loan in 4 years (48 months). You want to figure out what your monthly payments will be if you buy this car. You are to determine the minimum monthly payment you must make in order to pay off this loan in four years.
Instructions
- Add the assumption table above to the top of your spreadsheet.
- Add the following below the assumption table. Note that you will have to calculate the formulas for every month from 1 through 48. There will be 2 cell references you need to lock to make this easier.
- After entering the formulas for Month 1 and locking the cell references you should be able to use the Fill Handle to drag the formulas down to Month 48. Be aware that until all the columns are finished the numbers will not look right. Be sure that you fill all columns before you get worried about your numbers looking strange.
- Make sure you give your spreadsheet a title that has something to do with the contents.
- Check for any numbers that must be in currency style or percent style and format the spreadsheet so that it looks nice.
- You have probably noticed that in Month 48 in the Amount Owed column is not equal to $0.00. In fact it should be $16,165.13. This is because the Monthly Payment is too low. You will now need to change the Monthly Payment to find what number will allow you to pay off the loan in 4 years. Change the Monthly Payment then check Amount Owed at month 48. Does it equal $0.00? If not try another number. Yes, the correct monthly payment will include cents so be sure you can see at least two decimal places.
- In your own words, explain the purpose of assumption tables. You can put this in your spreadsheet, or create a word processing document for it.
