Financial analysis
PV function: find the maximum loan a monthly budget can support
Discount a fixed monthly repayment stream back to today’s affordable loan principal with PV, and verify how rising interest rates shrink borrowing capacity.
Work backward from a fixed monthly budget
Suppose your delivery van budget caps repayments at 250 per month (-250 in B3) over 24 months (B2) at a 6% annual interest rate (0.06 in B1). While PMT solves for payment from principal, PV solves for principal from payment.
| Input | Cell | Value |
|---|---|---|
| Annual interest rate | B1 | 0.06 |
| Months (nper) | B2 | 24 |
| Affordable monthly payment (pmt) | B3 | -250 |
Discount monthly payments at rate / 12
=ROUND(PV(B1/12, B2, B3), 2) returns +5,640.72—meaning 24 monthly payments of 250 at 6% nominal annual interest finance 5,640.72 of equipment today (with the remaining 359.28 going to interest).
=ROUND(PV(B1/12,B2,B3),2)Zero-rate upper bound and higher-rate sensitivity
At 0% interest, PV equals the full 24 * 250 = 6,000. Raise B1 from 0.06 to 0.12 (12% nominal annual interest) in the practice grid to see borrowing power drop to 5,310.85.
Assumptions in this example
This example uses a constant nominal annual interest rate divided by 12 and payments at the end of each month. Fees, taxes and changes in rates are excluded. Actual lender or savings terms can differ.
Try the example
Use the original values or change them to test your understanding.