Mad Marv’s Place

I’m reposting some of my articles I wrote during a spreadsheet education project on The Motley Fool. The complete thread can be found here (Guest registration required for non-members):

Here are some of the most commonly used financial functions in Excel for investing. They are not the only financial functions that can be found in Excel. I am just filtering the ones I think are most useful for investors. All of the financial functions are basically derived from the compound interest formula:

Future_Value = Present_Value * (1 + Interest_Rate) ^ Time

For example, $1000 is deposited in a 5-year CD earning 5% compounded annually. Over the next five years the balance would be:

Year 1: $1000.00 * (1 + 5%) = $1050.00
Year 2: $1050.00 * (1 + 5%) = $1102.50
Year 3: $1102.50 * (1 + 5%) = $1157.63
Year 4: $1157.63 * (1 + 5%) = $1215.51
Year 5: $1215.51 * (1 + 5%) = $1276.28

Now, instead of going through the calculation for each year, the ending value at Year 5 could have been calculated from the compound interest formula:

$1000 * (1 + 5%) ^ 5 = $1000 * 1.27628 = $1276.28

The compound interest formula can be tweaked to work with more frequent compounding (say monthly or quarterly) by adjusting the Interest_Rate and Time values accordingly.

Monthly compounding:

Future_Value = Present_Value * (1 + (Interest_Rate / 12)) ^ (Time * 12)
$1000 * (1 + 5%/12) ^ (5*12) = $1000 * 1.28336 = $1283.36

Quarterly compounding:

Future_Value = Present_Value * (1 + (Interest_Rate / 4)) ^ (Time * 4)
$1000 * (1 + 5%/4) ^ (5*4) = $1000 * 1.28204 = $1282.04

Incidentally, if the splitting of compounding periods is taken to its extreme (i.e. continuous compounding), the compound interest formula becomes the exponential growth formula e ^ Interest_Rate * Time.

$1000 * e ^ (5% * 5) = $1000 * 1.28403 = $1284.03

In Excel, the compound interest equation is made into several related functions. Each function will solve for a missing variable if given enough of the other variables. Think of it as a family of functions where one function will solve for C when given A, B & D. Another function can solve for B if given A, C & D, and yet another will solve for D given A, B & C.

=PV(rate,nper,pmt,fv,type)        Present Value - calculates the principal amount of a loan.
=FV(rate,nper,pmt,pv,type)        Future Value - calculates the final value of a savings account.
=PMT(rate,nper,pv,fv,type)        Payment - calculates the periodic payment/deposit.
=NPER(rate,pmt,pv,fv,type)        Number of Payments - calculates the number of payments to
                                                 pay off a loan or the number of deposits to an account.
=RATE(nper,pmt,pv,fv,type,guess)  Interest Rate - calculates the interest rate of a
                                                      loan/savings account.

The boldfaced items are the minimum required inputs for the function to work.

“Type” is optional and refers to when the payments are made, 0 for end of the period, 1 for the beginning. If the type is omitted, it is assumed to be 0. The rate and nper must be consistent with the timeframe. If annual payments are assumed, the straight NPER and RATE values can be used. If monthly payments are assumed, the RATE must be divided by 12 and the NPER multiplied by 12.

This family of functions is used to calculate, for example, the respective parts of a series of equal cashflows, such as monthly payments on a loan or the ending balance of a savings account.

Cash outflows (such as a withdrawal of funds from an account) are listed as negative, cash inflows (such as deposits) are positive. Keep in mind that the negative values are needed to offset the positive for the function to operate. Say you write a FV() function to calculate the expected balance of a savings account.

Annual $1000 deposits, 2% interest and 5 years are plugged in as

=FV(2%,5,1000)

The output is -$5,204.04, but don’t panic, this does not mean you lost money or have a bug in your formula. The negative value is necessary to the function because it needs to balance positive and negative cashflows. Your ending balance will be $5,204.04, but Excel will report the output of the FV() function as -$5,204.04. You will have to remember how the sign convention works.

Examples:
You are considering a 30-year mortgage at 6% interest. If you wish to keep the monthly payment below $1000, how much can you borrow?

=PV(0.06/12,30*12,-1000)
=$166,792

If you needed $250,000, how much would the monthly payment be under the same terms?

=PMT(0.06/12,30*12,250000)
=-$1,499

If you deposited $100/month into a savings account at 2% interest, how long would it take to save $5000?

=NPER(0.02/12,-100,0,5000)
=48.1

If you were to save $125 instead of $100 how much would the account be worth at the end of 48 months?

=FV(0.02/12,48,-125)
=$6,241

If you bought a $100k annuity that paid $12k a year 10 years, what is the interest rate?

=RATE(10,-12000,100000)
=3.46%

Internal Rate of Return
IRR(values,guess)
XIRR(values,dates,guess)

These functions are often used in determining the performance of a portfolio with cash inflows and outflows. A portfolio that is setup with a single deposit and is allowed to grow without additions or withdrawals can be measured using the PV/FV family of functions. When multiple cashflows are involved, it gets a bit trickier. Excel can solve these types of problems with IRR() and XIRR(). These functions are iterative, meaning the solution is calculated through trial and error.

IRR() is used for cashflows that fall into regular intervals, usually annual.

XIRR() is used for irregular cashflows the corresponding date for each cashflow must be entered. XIRR() may not be installed in the default Excel installation. If you get #NAME? as the result of the XIRR() function, it means that XIRR() has not been installed. To install XIRR(), go to the Tools menu, pick Add-Ins… and make sure Analysis Toolpak is installed.

The guess part of the function is optional. If it is not entered it is assumed to be 0.1.

Excel calculates the IRR/XIRR by trial and error and the guess is only to give Excel a starting point.

Example: A $10k stock portfolio is bought on 1/1/2004. Dividend payments are received at the start of each subsequent year according to the table below.

1/1/2005   500
1/1/2006   750
1/1/2007   650
1/1/2008   700
1/1/2009   750

On 1/1/2009 the portfolio is liquidated for $13k. The return on the portfolio is calculated with the IRR() & XIRR() functions. The dates are listed in Column A, cashflows are in Column B.

           Col A        Col B
Row 1  1/1/2004  -10000
Row 2  1/1/2005       500
Row 3  1/1/2006       750
Row 4  1/1/2007       650
Row 5  1/1/2008       700
Row 6  1/1/2009    13750
                   =IRR(B1:B6)
                   =XIRR(B1:B6,A1:A6)

The result of the IRR() function is 11.38%, the result of XIRR() is 11.37%. The difference is due to leap years. Below is the same example with different dates for dividend payments.

Note that IRR() cannot be used at all in this example.

            Col A        Col B
Row 1    1/1/2004  -10000
Row 2  2/14/2004       500
Row 3  3/15/2005       750
Row 4    4/1/2006       650
Row 5    7/4/2007       700
Row 6 10/31/2008       750
Row 7    1/1/2009    13000
                  =XIRR(B1:B7,A1:A7)

The result is 11.81%.

Net Present Value
NPV(rate,value1,value2,…) & XNPV(rate,values,dates)

Similar to the PV() function, these functions calculate a series of unequal cash flows. NPV() calculates the cashflows assuming a regular interval, XNPV calculates them at irregular intervals. “rate” in this case refers to the discount rate and is assumed to be constant throughout the calculation. In practice the discount rate should be your assumed rate of return + a safety factor. This is the main function used in discounted cash flow analyses.

Examples:

The intrinsic value (IV) of a security is the discounted sum of all future cashflows. This is usually done with a two-stage growth model. The first stage is the defined or explicit growth period where the cashflows and growth rates can vary from year to year. The second stage is the terminal or residual growth period where a constant growth rate is assumed. Typically the first stage runs for 10 years and all later years use the 10th year’s cash flow as a base for the future projection.

The cashflows for our example company from Years 1 - 10 are explicitly determined and shown in the table below. Years 11 and on are estimated by taking Year 10’s cashflow and growing it into perpetuity at a constant rate. A 12% discount rate is assumed.

          Year      Cashflow
          Col A     Col B
Row 1    1        $1.00
Row 2    2        $1.20
Row 3    3        $1.33
Row 4    4        $1.45
Row 5    5        $1.28
Row 6    6        $1.56
Row 7    7        $1.68
Row 8    8        $1.82
Row 9    9        $1.75
Row 10   10       $2.00

The cashflow from each year could be discounted individually in Column C using =B1/(1+.12)^A1,
=B2/(1+.12)^A2, etc. but Excel has a function, NPV(), that can sum the cashflows in a single equation.

The NPV of the 10 years is:
=NPV(0.12,B1:B10)
=$8.00

For Years 11+, a 5% growth rate is assumed. Excel does not have a function to calculate a perpetuity but luckily the algebra for this infinite series is very simple, lol. Here’s a link (Note: Guest registration required) to a post that goes through the math.

Year 10’s FCF/share is $2.00. The Year 10 value of the perpetuity is

=2.00*(1+0.05)/(0.12-0.05)
=$30.00

This is not the PV of the perpetuity. The equation used to solve the perpetuity calculates an equivalent single cashflow at Year 10. This needs to be discounted back to the present using the following equation.

=30/(1+0.12)^10
=$9.66

Adding it to the discounted cashflows from Years 1 - 10 yields 9.66 + 8.00 = 17.66

It may be easier to visualize the two parts of the DCF analysis with this chart.

The residual growth period theoretically goes on into infinity and so does the growth, but as you can see the discounted value of the cashflow shrinks to an infinitesimal amount. In practice the cashflows from the first dozen years or so will be equal to about half the IV.

The first 20 years captures about two-thirds to three-fourths and the first 50 years captures 90%+ of the IV depending on the discount rate chosen.

Yield - YIELD (settlement,maturity,rate,pr,redemption,frequency,basis)
This function calculates the yield of bond. Settlement is the purchase date, maturity is the maturity date. Rate is the annual coupon rate (the interest rate you get if you buy the bond at par value). Redemption is the redemption price per $100 face value (i.e. a $99 redemption price is equal to $990 on a $1000 bond). Frequency refers to how many interest payments are made per year. Basis is optional and refers to how days our counted (i.e. 30 days per month, 360 per year).

The true yield of a bond over its lifetime (otherwise known as the Yield to Maturity or YTM) is somewhat difficult to calculate because the price of a bond fluctuates and the bond has a limited lifespan. For example take a bond w/ $1000 face value and 5% coupon rate paid semi-annually has a 5-year maturity. Half way through the first year the bond’s price on the open market is $1025 because interest rates have changed since the bond was issued. A bond investor buying at $1025 may have a current yield of 50/1025 = 4.88% but when the bond matures the investor will only get back $1000, not the $1025 paid on the open market. The YTM is somewhat lower and can be calculated with the YIELD().

=YIELD("1/1/04","1/1/09",5%,102.5,100,2)
=4.44%

Tags: Investing //