So if the same problem above was a monthly payment of $1000 for 12 years at a 5 percent interest rate, the formula you would enter would be =PV(.05/12,12*12,1000), or you could simplify it into =PV(.004167,144,1000). Because the interest rate is an annual rate, you would also have to make this a monthly rate by dividing it by 12. This means that if you get a payment each month, you would have to multiply the number of years by 12 in order to get the number of months. This would get you a present value of $8,863.25.įor this formula, it is important to note that the “NPER” value is the number of periods that the interest rate is for, not necessarily the number of years. NPER is the number of periods with that discount rate, andĮxample: if you were trying to figure out the present value of a future annuity that has an interest rate of 5 percent for 12 years with an annual payment of $1000, you would enter the following formula: =PV(.05,12,1000).RATE is the discount rate or interest rate,.The basic annuity formula in Excel for present value is =PV(RATE,NPER,PMT). Excel can perform complex calculations and has several formulas for just about any role within finance and banking, including unique annuity calculations that use present and future value of annuity formulas. Excel can be an extremely useful tool for these calculations. Knowing exactly what it means to discount something or to get the future value of a particular investment vehicle is necessary to do the job. By Excel Tips and Tricks from Categories: Advanced Excel Tags: Annuity Formula Excelįor anyone working in finance or banking, the time value of money is one topic that you should be fluent in.

Drawdown calculation in excel