Microsoft logo
Excel logo

Excel Formula Coach

 Office logo
 

Plug your numbers in the boxes below, press the Build it! button, and we'll build your formula. We'll even give you the answer.

When you're done, you can paste the formula into your own Excel worksheet.

Interest rate:
(such as 4.5)

Length of loan:
(in years, such as 30)

Payment:
(such as -1250)

Ready?

This is your formula:

And here's your answer:

Try another?

PV function, scenario #1: Use it to find the present value of a loan

Returns the principal amount of a loan, based on a steady interest rate, regular payments, and a set number of payment periods.

For example, you want to purchase a home — you can get a 4.5% interest rate, you'll have 30 years to repay the loan, and you've budgeted a $1,250 per month payment. Use PV to find the loan amount (the present value) you can afford. The answer is $246,701.45.

Syntax PV(rate, nper, pmt, [fv], [type])

Tip Wondering why [fv] and [type] are in square brackets? The brackets mean they're optional. If you don't include values for fv and type in your formula, Excel assumes your balance will be $0 at the end of the loan, and that your payments are due at the end of the period.

Example =PV(4.5%/12,30*12,-1250)

The PV function has the following arguments — the first three are always required:

Rate (Required) The interest rate. For monthly payments, divide this by 12.

Nper (Required) The total number of payments. For monthly payments, multiply this by 12.

Pmt (Required) The payment amount — enter this as a negative number.

Fv (Optional) The future value, or the balance you want after the last payment is made. If you don't include a value for fv, it's assumed to be 0 (zero), that is, the future value of the loan is 0.

Type (Optional) The number 0 (zero) means the payments are due at the end of each period, and 1 means they're due at the beginning. If you don't include a value for type, it's assumed to be 0 (zero).
 


Here's your chance to leave feedback!

Would you like to see more Excel functions featured in Excel Coach? Do you have an idea for improving the design? Let us know by going to the PV function article and leaving feedback at the end, under the "Did this article help you?" section. It'll help us better identify feedback on this if you mention "Excel Coach" in your comment.

Did this article help you

Choose one of the three responses, leave a comment in the "Rating received!" box, and click the "Submit" button. We'll read it...we promise.

Feedback box

Thank you!

Microsoft

© 2014 Microsoft Corporation. All rights reserved.