Excel 2013 & 2016: Using PV to determine how much you can afford to
borrow

Updated 2014.01.09

# Objective:

Use the **PV** function to determine how much a series of future
payments would be worth to you today. This function can be used to determine
how much money you can afford to borrow. If you know how much you can afford to
pay each month, you can work backwards to determine how much you can afford to
borrow (present value).

# PV arguments:

·
**RATE:** Rate per period, not rate per year.

·
**NPER:** Number of periods, not number of
years.

·
**PMT:** Payment. If you are doing a savings
problem, the amount you are depositing each period. If you are doing a loan
problem, the amount you are paying to re-pay the loan each period. This number
is __always negative__.

·
**FV:** Future value. The amount you will
have at the end of the number of periods if you are doing a savings problem, or
the amount you will owe at the end of the number of periods if you are doing a
loan problem (this is usually, but not always, 0).

·
**TYPE:** Type of payment. 1=payments at the
beginning of the period, 0=at the end of the period.

## Example: How much
can you afford to borrow if you know how much you can afford to make in regular
payments?

Assume that you know you can afford
monthly payments of $1,000 to pay off a loan. The loan is for 30 years, and the
interest rate is 8.00% (annual rate). You make payments every month at the end
of the month. The balance owed at the end of the 30 years will be $0. How much
can you afford to borrow?

#### Solution

The values we can identify are:

·
Payment: $1,000

·
Years: 30

·
Annual Rate: 8.00%

·
Ending Balance: $0

·
"You make payments every month…"
means that Periods Per Year is 12

·
"… at the end of the month"
means that Beginning/End value is 0.

·
30 years at 12 periods per year means that the
Total Periods is 360

·
Rate per Period: 8.00% / 12 = 0.6666667%

The PV arguments are: PV(**rate, nper, pmt, **fv, type)

So the formula is:

=PV(0.66666667%, 360, -1000, 0, 0)

Note again that the **pmt**
argument is negative.