Calculating Holidays like Victoria Day

I recently was asked how to use an Excel formula to calculate the date for the Canadian holiday Victoria Day. This holiday is defined as the "Monday on or before May 25th".

To handle this type of calculation, I worked out the following general formula for the "Day of the Week on or before a given Date". The variable DoW below equals 1 for Sunday, 2 for Monday, etc. The Date variable is a reference to a date or a date created using a function like DATE(2010,5,25).

=Date-MOD(WEEKDAY(Date,1)-DoW,7)

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