Skip to content

3. Create a Loan Amounts Table

Playback Speed:
Transcript

In this video, you will create a table to calculate and compare monthly payments for different loan amounts.

You will use absolute and relative cell references to perform these calculations.

First, take a moment to think about how you want to simulate buying the car.

Maybe you are actually interested in buying a car soon, so you want to look in the price range that a student could afford.

Or, maybe you would like to pretend that you are a celebrity, with a much higher price range.

That is okay.

This activity is a simulation, so you can be anyone you like!

Type “Buying for” in another column.

In the cell beside it, type who is buying the car in your simulation.

Then, type “Price Range” and fill in an appropriate range.

For example, a high school student might be able to get a car loan for up to 15 thousand dollars.

A celebrity, on the other hand, might be able to get a loan for 50 to 100 thousand dollars.

This example is for a high school student, and the price range is one thousand to 15 thousand dollars.

Use whatever range is appropriate for the buyer you choose.

Type “Loan Amount” and “Monthly payment” in cells at the bottom of your summary table.

In the first column, type a value in your range for potential loans.

This example starts with one thousand dollars.

In the cells beneath, increase the loan amount in equal increments, like 50, 100, or 1000 dollars.

In this case, the loan amount increases by 500 dollars in each cell.

To repeat this pattern for the rest of your spreadsheet, highlight at least two cells.

Drag the cell handle down.

Now the loan amounts appear in the same increments without you having to type in every value.

Next, calculate the monthly payment for each loan amount.

To do this, use the PMT, or “periodic payment for an annuity investment,” function.

This function returns the amount you would pay for a loan based on the APR and loan term.

Beside your first loan amount, type “equals PMT.”

The autofill menu appears and gives you help text with a summary of the function and its parameters.

“Rate” is the first parameter, measured in months.

Select the cell where you calculated the monthly APR.

Type a comma, then click the cell with the number of payments, or periods.

Type another comma, then select the cell with the loan amount.

Close the parentheses and press enter.

The monthly payment calculates in the cell.

Next, drag the cell handle down a few rows to copy the formula.

Uh oh!

Something isn’t right here.

The formula does not copy properly.

Go back to the cell you calculated and check the formula.

Everything is right: Monthly APR Number of payments And loan amount.

Check the formula for the next cell.

There’s the problem!

The cell references for number of payments and APR are relative, not absolute.

That means they change based on the relative position of rows and columns.

In this case, you do NOT want the monthly APR or the number or payments to change.

To fix this, create absolute cell references for the first and second parameters.

Click on the cell with the original function.

In the monthly APR parameter, enter a dollar sign before the column letter and before the row number.

Do the same in the second parameter, for rate.

These values do not change in your calculations.

The last parameter is loan amount.

This cell reference remains relative, because you want the value to change in the function based on the relative position of the loan amount.

Press enter to complete this function.

The calculated total remains the same as before.

Drag the formula to match the length of the loan amount column.

Now the functions calculate automatically and correctly.

Great!!

If functions or formulas return an error message, check for absolute and relative cell references.

Chances are you need to add an absolute reference.

Now, it’s your turn Add a target car buyer and estimated price range to your spreadsheet.

Create columns for “Loan Amount” and “Monthly Payment” to create a table below the frozen rows.

Enter a range of potential loans, increasing incrementally.

Calculate the monthly payment for each loan amount using the PMT function.

Change the monthly APR and loan term parameters to absolute instead of relative references.

Next, copy the function to the rest of your spreadsheet.

Then, move on to the next video to reformat your spreadsheet values.

Next
Instructions
  1. Add a target car buyer and estimated price range to your spreadsheet.
  2. Create columns for Loan Amount and Monthly Payment to create a table below the frozen rows.
  3. Enter a range of potential loans, increasing incrementally.
  4. Calculate the monthly payment for each loan amount using the PMT function.
  5. Change the monthly APR and loan term parameters to absolute instead of relative references.
  6. Copy the function to the rest of your spreadsheet.