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 both relative and absolute cell references to make these calculations throughout the spreadsheet.

First, take a moment to think about what cars you’d like to explore for this activity.

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

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

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

Type “Buyer” in a new column.

In the cell beside it, type the car buyer.

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 or 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.

Now, begin your data table.

In the first column beneath the summary table, 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.

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

Then, drag the cell handle down.

Now the loan amounts appear in the same increments.

You don’t have 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, under the Monthly Payment cell, type “equals PMT.”

The autofill menu appears 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.

The PMT formula calculates how much money you would pay to a lender, so it appears in your spreadsheet as a negative number.

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.

Click on the cell with the original formula.

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

Do the same in the second reference, for rate.

These values do not change in your calculations.

The last reference 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 the cell references.

You may need to add an absolute reference.

Now, it’s your turn: Add a prospective 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.

Make the monthly APR and loan term absolute instead of relative cell references.

Copy the function to the rest of your spreadsheet.

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

Next arrow_forward
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 trable 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.
Attributions
  • "File:Wrapped Porsche 918 Spyder.jpg" by Axion23 (https://commons.wikimedia.org/wiki/File:Wrapped_Porsche_918_Spyder.jpg) -- Licensed by CC BY 2.0 (https://creativecommons.org/licenses/by/2.0/deed.en) -- Image scaled up, cropping edges
  • "New Car" by Caitlin Regan (https://www.flickr.com/photos/caitlinator/3673390863) -- Licensed by CC BY 2.0 (https://creativecommons.org/licenses/by/2.0/) -- Image scaled up, cropping edges