 Excel 2019/365: 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. 