rate - Required. Interest rate per period (annual rate ÷ 12 for monthly)
nper - Required. Total number of payment periods
pmt - Required. Payment made each period (use negative for outflows)
fv - Optional. Future value (default: 0)
type - Optional. 0=payment at end, 1=payment at beginning (default: 0)
| A | B | C | |
|---|---|---|---|
| 1 | Payment | Rate | Term |
| 2 | $2,000 | 4.5% | 30 years |
| 3 | |||
| 4 | Loan Amount: | =PV(B2/12, C2*12, -A2) $394,478 |
Monthly payment $2,000 for 30 years at 4.5%
=PV(0.375%, 360, -2000) → $394,478
Annual $100K payments for 20 years at 5%
=PV(5%, 20, -100000) → $1,246,221
$1,500/month lease for 36 months at 8%
=PV(0.667%, 36, -1500) → $48,135
$500/month for 10 years to reach $100K
=PV(0.5%, 120, -500, 100000) → $-99,540
Use the PV function in Excel to determine the maximum mortgage amount you can afford based on your monthly payment capacity. Critical for homebuyers planning budgets, real estate agents advising clients, and mortgage brokers qualifying borrowers. The Excel PV formula converts affordable monthly payments into total loan principal, accounting for interest rates and loan terms to provide accurate home price affordability calculations for residential and commercial property financing.
| A | B | C | |
|---|---|---|---|
| 1 | Input | Monthly Payment | $2,000 |
| 2 | Input | Annual Rate | 4.5% |
| 3 | Input | Years | 30 |
| 4 | |||
| 5 | Result | Max Loan: | =PV(B2/12, B3*12, -B1) $394,478 |
Calculate the present value of lottery annuity payments to compare against lump sum offers using the PV function in Excel. Essential for financial advisors helping lottery winners, insurance companies valuing structured settlements, and individuals choosing between payment options. The Excel PV formula applies discount rates to future payment streams, revealing true current value for informed financial decisions in retirement planning and settlement negotiations.
| A | B | C | |
|---|---|---|---|
| 1 | Input | Annual Payment | $100,000 |
| 2 | Input | Years | 20 |
| 3 | Input | Discount Rate | 5% |
| 4 | |||
| 5 | Result | Present Value: | =PV(B3, B2, -B1) $1,246,221 |
❌ The Problem:
✅ Solution:
=PV(4.5%/12, 360, -2000)Divide annual rate by 12 for monthly periods. Rate and nper must use the same time unit - if nper is in months, rate must be monthly. The PV function in Excel requires consistent period units to calculate accurate present value.
❌ The Problem:
✅ Solution:
=PV(5%/12, 360, -2000)Use negative payment for money you pay out (loan payments). The Excel PV formula follows cash flow conventions: negative for outflows, positive for inflows. For loan payments you make monthly, use negative values.
❌ The Problem:
✅ Solution:
=PV(4%/12, 360, -2000)Be consistent with payment frequency. For monthly $2,000 payments: use 360 periods (30×12) and monthly rate (4%/12). The PV function in Excel requires all time-based inputs to use the same period unit.
Master these PV Function variants:
From basics to advanced - AI generates perfect formulas instantly.