Spreadsheet Lab #2

Loan Calculator

LOAN CALCULATOR

Suppose you wish to purchase a new home or new car and you want to see how much the payments will be, how fast your equity will increase as you pay off the loan and what the yearly tax writeoff will be by finding the total amount of interest you paid in a given year. Construct a spreadsheet solution as described below. Even if your spreadsheet has built in payment calculations, don’t use them. Use the formulas provided below.

Step 1 - Input:

The first three cells on the sheet will be used for user input, not formulas. These cells will start with values that control all the other output values on the sheet. ONLY these three values will be input in order to calculate any given loan. All the other values are calculated by formulas. Make sure to label these cells appropriately.

ACTUAL CELL LABEL (NAME)

Refer to the first cell as T => Interest Rate per payment period.

Refer to the second cell as A => Amount of Loan.

Refer to the third cell as Y => Length of Loan in Years.

Step 2 - Calculate:

Calculate each of the following values using formulas. Make sure these cells are also labeled clearly:

First, we must calculate the interest rate per month using the interest rate per year that is entered above. This is done by dividing the "T" cell by 12. The calculation will be:

I = T / 12

Let another cell (called N) = total number of payment periods.

(12 per year, 5 years = 60 payments).

Let another cell (called V) = 1 / (1 + I). Label it V-factor.

Let another cell (called K) = the current payment number.

K = N / 2.

This will show the payment, balance, etc. for half-way through the loan.

Step 3 - Calculate - Using Formulas Above:

Next, write five more cell formulas using those created in the section above. Note: VN means “V to the power of N” which is done with the “^” key in Excel. A formula raising the value in C8 to the power of the value in C10 would be: =C8^C10

a) Monthly Payment = (I * A / (1 - Vⁿ⁾⁾

b) Balance (after payment number K) = (A / V^{k) * ((Vk - Vn) / (1 - Vn ))}

c) Interest amount (of payment K) = I * remaining Balance.

d) Principal paid (for payment K) = Monthly Payment - Interest for payment K.

e) Total of payments = Monthly Payment * N.

Step 4 - Input New Values:

Once the sheet is built, the only entries you should make in actual numbers are the loan amount, the number of years and the interest rate. All other entries are to be calculated by the spreadsheet formulas. Print out the same spreadsheet with different values as follows:

Loan # Years Interest Rate Amount

1 3 12% $20,000

2 5 12% $20,000

3 15 8% $100,000

4 30 8% $100,000

In each case, you should use a K (current payment number) equal to half the loan length. This will show how much you will still owe, how much interest you have paid, etc. half way through the loan. Print the cell formulas plus the results of all your calculations. Present your results in a readable format, with labels next to the input cells at the top of the spreadsheet to tell you what to enter, and with the calculated information displayed with readable labels.

What to turn in:

Print the cell formulas plus a hardcopy of the results that you get from your spreadsheet for each of the different loans. The loan results may all be printed on one spreadsheet or you can create a separate spreadsheet for each case. Remember to use Ctrl – Backwards Quote to get the cell formulas to appear. Hit the same keys to go back to the original view. The ` key is under the ~ key on the top left edge of your keyboard.

To check your answers, here are the correct values for the first loan:

Payment	$ 664
Balance	$ 10,893
Interest	$ 109
Principal	$ 555
Total of Payments	$ 23,914