Skip to content

4. Absolute Values and Currency Formatting

Playback Speed:
Transcript

At this point, your spreadsheet returns the monthly payment amounts as a negative number with many decimal points.

In this video, you will add an absolute value formula to return only positive numbers and reformat the monthly payment cells as currency.

In the last video, you changed some relative cell references to absolute references.

But something is still off about these calculations.

With this function, the spreadsheet returns a negative value for each of these calculations.

This would work well in a budgeting spreadsheet where you wanted to subtract a payment amount each month.

But in this case, you do not need a negative number.

To correct this, convert these values to an absolute value, so that they always appear as positive numbers.

Return again to the original formula cell.

Place the cursor between the equals sign and “PMT.”

Type “ABS” and an open parentheses.

The autofill menu appears.

Make sure you are using the absolute value formula.

Then, close the parentheses at the very end of the formula so that they enclose the entire PMT function.

Press Enter.

The number appears as an absolute value.

Drag the formula down to copy the revised function.

The monthly payment is now displayed as a positive number, but each has many decimal points.

To reformat these cells to reflect currency, highlight the cells in the monthly payment column of your table.

Then, change the format to “currency.”

All of the numbers appear as dollars and cents.

Format the “Loan Amount” column of your table the same way.

Good job!

Compare some of the values in the spreadsheet you created.

A 3-year loan for one thousand dollars at a 4 percent APR would cost about 30 dollars a month.

If you borrowed 5 thousand dollars, it would cost you close to 150 dollars a month.

And a loan of 10 thousand dollars would cost you nearly 300 dollars a month.

Now, it’s your turn: Insert an absolute value formula within the PMT function to return all positive values.

Change the cell format in the “Loan Amount” and “Monthly Payments” columns to “Currency.”

Compare the monthly payments for several loan amounts.

Consider which monthly payment you could reasonably afford.

Then, move on to the next video to calculate how much interest you would pay with each loan amount.

Next
Instructions
  1. Insert an absolute value formula within the PMT function.
  2. Change the cell formats to "Currency."
  3. Compare the monthly payments for several loan amounts.