How to use Excel PV Function

    how to use excel pv function

    Quick Intro

    Want to check if your investment is profitable? Use PV Function. It will help you to determine that an investment is profitable or not.

    PV Function is one of the multi-usage functions in excel. You can use it to calculate the present value of a financial investment or a loan.

    And, in the case of a loan, you can get the actual present value of a loan you can afford to pay in monthly payments. For using PV function in excel, you need a constant interest rate, constant periodic payments.

    Let’s say if you have to pay $2000 every year for next 20 years to get $50000 at the end of 20 years. It will tell you that it’s profitable or not.

    Syntax

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

    • rate The rate of interest for the payment of the loan.
    • nper Total number of payment periods
    • pmt A constant amount of payment you have to make after every period. 
    • [FV] The future value or a cash balance of a loan or an investment you want to attain after the last payment is made. If omitted, it will b assumed as 0.
    • [type] Time of the payment. Beginning of the period (use “0”) or end of the period (Use “1”).

    More Information on Excel PV Function

    • The units you use as arguments should be consistent. For example, If you are using periods in months (36 Months = 3 Years) then you have to convert the annual interest rate into a monthly interest rate (6%/12 = 0.5%).
    • PV function is an annuity function.
    • In annuity functions, the cash payments by you are represented by negative numbers and the payments you receive are represented by positive numbers.

    Example

    Let’s say you want to invest $4000 in an investment plan and in return, you’ll get $1000 at the end of each year for next 5 years.

    That means you’ll get total $5000 in next 5 years.

    Now, the thing is, you have to evaluate that this investment is profitable or not. As you are investing $4000 today and the return will come to you in next 5 years.

    Here, we are using 5% as an interest rate.

    how to use excel PV function learn with examples

    In above calculation, when we used PV function it has returned -4329.

    So, here the present value of your investment is $4329 and you are investing $4000 for it. Hence, your investment is profitable.

    Quick Tip: If the present value of the investment(inflows) is greater the cost of the investment, the investment is a good one.

    Sample File

    download sample file to learn more about this tips

    What’s Next?

    To learn more about Excel PV Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.

    There are also you have FV | PMT  functions which are highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.