Annuity Formula

The PV, FV, NPER, RATE, and PMT functions in Excel can be used for both an ordinary annuity (payments made at the end of the period, type=1) and annuity due (payments made at the beginning of the period, type=0).

The PMT function can be used to calculate the annuity payment amount given the annual interst rate (i), number of payments (n), and initial principal (P).

A =PMT( i, n, -P, 0, type)

The PV function can be used to calculate the present value of the annuity. When the payment amount represents withdrawals from a retirement account, the present value would represent the initial principal:

P =PV( i, n, -A, 0, type)

The FV function can be used to calculate the future value of an annuity:

F =FV( i, n, -A, 0, type)

The NPER function can be used to calculate the number of payments:

n =NPER( i, -A, P, 0, type)

The RATE function can be used to calculate the interest rate. It requires iteration and an initial guess at the rate (default is 0.1):

i =RATE(n, -A, P,0, type, guess)

You can also use these functions for an inflation-adjusted annuity payment, which is actually an exponential gradient series cash flow. See Annuity for the details.

NPV Formula

To calculate Net Present Value using the NPV formula in Excel, you need a series of periodic cash flows like that shown in the figure below.

The NPV can then be calculated using the following formula:

=NPV(rate,values_t1_to_t4)+value_t0
=NPV(D2,B3:B6)+B2

The Excel NPV formula only calculates the sum of the present value of future cash flows (t=1, t=2, ... t=n), so to obtain the true Net Present Value, you need to subtract the initial investment or in other words, add the initial negative value at t=0.

The NPV formula requires periodic cash flows. If you have non-periodic cash flows, you can use the XNPV formula.

Download an NPV Calculator for Excel.

IRR Formula

To calculate Internal Rate of Return using the IRR formula in Excel, you need a series of periodic cash flows like that shown in the figure below.

The IRR can then be calculated using the following formula, with 0.1 being the initial guess at the rate:

=IRR(values,guess)
=IRR(B2:B6,0.1)

The IRR formula requires at least one negative and one positive value. Normally, the negative value at t=0 represents the initial investment. The future cash flows can be negative or positive, but they need to be periodic (occuring at t=1, t=2, t=3, etc). If you have non-periodic cash flows, you can use the XIRR formula.

Download an IRR Calculator for Excel.

Exponential Growth Rate

This article explains how to use the LOGEST function in Excel to calculate exponential growth rate, how to get the growth rate from an exponential curve fit, and how to use Excel's GROWTH function to make future predictions.

Linear Interpolation In Excel

There are many ways to perform linear interpolation (or extrapolation) in Excel. The examples below assume that you know at least two points (x₁,y₁) and (x₂,y₂) and want to solve for y given x based on the equation for a line: y=mx+b, where m is the slope and b is the y-intercept.



1. Using the Point-Slope formula, where the slope is (y₂-y₁)/(x₂-x₁):

y=y1+(x-x1)*(y2-y1)/(x2-x1)
=B2+(B5-A2)*(B3-B2)/(A3-A2)

2. Using the SLOPE and INTERCEPT functions to solve for m and b:

y=SLOPE(y's,x's)*x+INTERCEPT(y's,x's)
=SLOPE(B2:B3,A2:A3)*B5+INTERCEPT(B2:B3,A2:A3)

3. The TREND function uses linear regression to solve for y if you have two or more points. This is also a way to estimate y from a linear fit of many points.

y=TREND(y's,x's,x,TRUE)
=TREND(B2:B3,A2:A3,B5,TRUE)

Factorial of Positive Non-Integer Values in Excel

I'm in the proces of creating a control chart template and needed to calculate the factorial of a non-integer value in Excel. Turns out that the factorial function, FACT(), only works for integers. Luckily, you can still get the factorial of a positive non-integer value by using the Gamma function. By definition, x! = G(x+1) = xG(x). In Excel, you have access to the GAMMALN() function which returns LN(G(x)), so to calculate x! for values greater than zero, you can use

=x*EXP(GAMMALN(x))

Ref: Factorial at Wikipedia.com

Array Formulas

Here are some tips related to the use of Array Formulas in Excel.

1. When using an Array Formula, you press Ctrl+Shift+Enter instead of just Enter after entering or editing the formula.

2. An Array Formula will show curly brackets around the formula in the Formula Bar like this: {=…}

3. Vector of 1's: The following formula will return an n x 1 vector of ones:
(1+0*ROW(OFFSET(INDIRECT("A1"),0,0,n,1)))

4. Sequential Number Vector (1, 2, 3, ...): To return an n x 1 vector of sequential numbers starting with 1, use the following formula:
ROW(OFFSET(INDIRECT("A1"),0,0,n,1))
or
ROW(INDIRECT("1:"&n))

Note: The INDIRECT function is used in these examples to avoid problems that would occur if cell A1 were deleted or if rows were inserted above cell A1 or columns were inserted to the left of A1.

Display Feet and Inches as 5' 8 1/2"

When converting metric distances to English units of feet or inches, you'll end up with a decimal value. But, you may want to display the value as feet and inches using the format 5' 8 1/2".

Let's say you have the decimal value 5.708333 for feet in cell A1. To display feet and inches, use INT(A1) to get the 5 and use A1-INT(A1) to get the remainder (inches). Multiple the remainder by 12 and then use the TEXT function to display the value as "# #/#" inches. Here's the formula:

=INT(A1)&"' "&TEXT(12*(A1-INT(A1)),"# #/#")&CHAR(34)

Round Price to the Nearest Nickel

If you want to avoid pennies, you can round prices to the nearest nickel using the following formula:

=ROUND(value/0.05,0)*0.05

If you want to round UP to the nearest nickel, you can change the above formula to use ROUNDUP instead of ROUND, or you can use the CEILING formula:

=CEILING(value,0.05)
=ROUNDUP(value/0.05,0)*0.05

To round DOWN, you can use the FLOOR formula:

=FLOOR(value,0.05)
=ROUNDDOWN(value/0.05,0)*0.05

Calculating Number of Working Days

=NETWORKDAYS(start_date,end_date,holidays)

You can use the NETWORKDAYS() function in Excel to calculate the number of working days (excluding weekends) between and including two dates. In Excel 2000/2002/2003, this function requires the Analysis ToolPak to be installed. The start_date and end_date must be dates stored as serial numbers or defined using the DATE function. The holidays parameter is optional and is a reference to range of dates to be excluded besides weekends.

Example:

A1=1/1/2009    (Start Date)
A2=1/8/2009    (End Date)
A3=1/1/2009    (Holiday)
A4=1/2/2009    (Non-Working Day)
A5=NETWORKDAYS(A1,A2)        (= 6 Working Days)
A6=NETWORKDAYS(A1,A2,A3:A4)  (= 4 Working Days)

The WORKDAY() function is almost the reverse of the NETWORKDAYS function, except that the WORKDAY function finds the date that is the number of days before or after the start_date, but not including the start date.

=WORKDAY(start_date,days,holidays)

Example:

=WORKDAY(A1,6)   (= 1/9/2009)