- Enter a car type in cell B1 - Select a car
that you are interested in purchasing, check out prices on the web
- Enter the Row labels in cells A2 to A9 (use
Alt+Enter to get words on 2 lines)
- Enter the table of years of loan versus interest rate in cell E1 to F6
- Enter Amounts in B2 and B3
- Instruct Excel to Calculate Amount Borrowed in B4
(type =, point at cell with price, type
-, point at cell with down payment, press
enter)
- Enter the number of years for loan in B5 (3, 4, 5, your
choice)
- Name the table in cells E3 to F5.
(Highlight the table, Insert menu, Name, Define, type a
short name)
- Enter the function to have Excel Look up the Interest Rate based on the Number of Years to Pay Off
(remember the spreadsheet rule #1, never type a value a
second time always point at it)
- In B7 instruct Excel to calculate the Monthly Payment using the appropriate function
- Have Excel calculate the Total paid out in cell B8 (the down payment
plus all the monthly payments)
- Have Excel figure out how much money you would have if you had NOT bought the car
and instead had put the down payment and all the monthly car payments in to the
bank.
- Create the payment schedule in cells through F47 by:
- Enter the 6 Labels in row 12 (use
Alt+Enter to get words on 2 lines)
- Enter a 1 in A13
- In B13, use (=point to) the amount of Loan in B4 as the beginning balance in month 1
(never type a value a second time always point at it).
- In C13, instruct Excel to calculate the interest paid the first month.
This is whatever was owed at the beginning of the month times the
interest rate for the month (Annual Interest Rate/12).
- Have Excel calculate the Amount the Loan was Reduced By in the first month.
This is the Monthly Payment (make it positive by
putting a "-" in front) minus the interest paid
this month.
- Have Excel calculate the amount owed after the first payment is made.
The Ending Balance is the Beginning Balance minus the amount by which
the Principle is Reduced.
- The cumulative Interest paid so far is simply the Interest paid this month.
- Second Month: Create the formula to have Excel calculate a 2 using the cell above, which contains a 1.
Or you can type a 2, then highlight both the 1 and 2 and drag them both
down the column for the correct number of row.
- The Balance at the beginning of month 2 is whatever is owed at the end of month 1,
so the formula simply points to it.
- Copy the formulas from Interest Paid, C13 down to C14.
- Notice that the Interest Paid is a crazy amount. That is
because both cell references are relative. Interest Rate should be Absolute. It absolutely never moves. so to fix
that:
- Name the Interest Rate (cell B6) using the cell to the left. Apply the name
to the Interest paid that month.
- Notice Principle Repaid has same problem.
So, name the Monthly Payment (cell B7) and Apply Principle
Reduction cell.
- The cumulative interest paid in month 2 is the cumulative interest in month 1 plus
the interest paid in month 2.
- Drag the formula in cell A14 to F14 down as far as necessary to pay back your loan
(if you overdo it and delete unnecessary rows.)
- Upload the sheet to your briefcase. Print the sheet for your
portfolio.
- Display the formula (Tools Menu, Options or Ctrl + ~),
Adjust column widths if needed.
- Set the Print Area
(Highlight what you want to print (just A1 to F16), File Menu, Print
Area, Set)
- Set the Page Setup to print column and row labels and gridline.
- Print the Formula.
- Reset the Print Area.
- Display numbers (not formula)
- Supply a different number for car print, down payment and number of years.
Watch every other number change!
|
