How do you calculate net present value in Microsoft Excel?

Answer 1

Enter the following formula: =PV(rate,periods,payment,FV,type) Rate is the interest rate you are using. Period is the number of periods, payment is the amount of any periodic payment (if there aren't any, enter 0). FV is the future value you want at the end of the investment (again, enter 0 if there is no remaining balance. Type is 1 if the first periodic payment is at the beginning of the period or 0 if the first payment comes at the end of the period. Remember to keep all of your terms in the same format of time - so if you are getting monthly payments, your periods must be expressed in months (not years) and your annual interest rate must be divided by 12. So, if you wanted to receive monthly payments of $100 for 10 years beginning at the end of month 1, and you wanted $5,000 left over at the end, and the annual interest rate was 8%, you would enter: =PV(.08/12,120,100,5000,0) If you just wanted to know how much you have to invest today at 8% to end up with $5,000 in ten years, the formula is: =PV(.08,10,0,5000,0) However, if the interest is compounded monthly, you would have to change the rate and term to months (.08/12 and 120).

Answer 2

Use the NPV function.

NPV(rate,value1,value2, ...)

Rate is the rate of discount over the length of one period.

Value1, value2, ... are 1 to 29 arguments representing the payments and income.

• Value1, value2, ... must be equally spaced in time and occur at the end of each period.
• NPV uses the order of value1, value2, ... to interpret the order of cash flows. Be sure to enter your payment and income values in the correct sequence.
• Arguments that are numbers, empty cells, logical values, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers are ignored.
• If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.