10 Ways to Analyze Financial Data Using Excel

All kinds of people use Excel, including scientists, engineers, mathematicians, statisticians, and pollsters. But if you could somehow survey all the world’s Excel users, the typical user would probably have something to do with the financial industry. Whether they’re accountants or adjusters, bankers or borrowers, or money managers or money lenders, financial types rely on Excel every day to analyze budgets, loans, investments, and other monetary minutiae.

But it’s not just the financial pros who count on Excel (sometimes literally). Financial amateurs can also use Excel to analyze mortgages, car payments, college funds, savings accounts, and other workaday finances.

Whether you make a living working with money or for money, these ten useful techniques for analyzing financial data using Excel might come in handy.

Calculating future value in Excel

If you have $1,000 and plan to invest it at 5 percent interest, compounded annually for ten years, the amount you’ll receive at the end of ten years is called the future value of $1,000. You can use the Excel FV function to calculate the amount you’ll receive.

Here’s the syntax of the FV function:

FV(<em>rate</em>, <em>nper</em>, <em>pmt</em>[, <em>pv</em>][, <em>type</em>])

The <em>rate</em> argument is the interest rate of the investment; <em>nper</em> is the term of the investment; <em>pmt</em> is the amount of each regular deposit into the investment; the optional <em>pv</em> argument is your initial investment; and the optional <em>type</em> argument is a number indicating when deposits are due (0 or blank for end of period; 1 for beginning of period).

For example, to calculate the future value of $1,000 when it's invested at 5 percent annual interest for 10 years, you use the following formula:

=FV(.05, 10, 0, -1000)

When you’re working with FV, cash outflows are considered negative amounts, so you need to enter the <em>pmt</em> and <em>pv</em> arguments as negative numbers.

If you plan on depositing an extra $100 per year in that same investment, the formula changes to this:

=FV(.05, 10, 100, -1000)

When calculating a future value, be careful of the values you use for the <em>rate</em> and <em>nper</em> arguments. If you're not making regular deposits or you are making a single deposit annually, you can use the annual interest rate for the <em>rate</em> argument and the number of years in the investment for the <em>nper</em> argument.

For more frequent deposits, you need to adjust the <em>rate</em> and <em>nper</em> values accordingly. As a general rule, divide the annual interest rate by the number of deposits per year, and multiply the term of the investment by the number of deposits per year. For example, with monthly deposits, divide the annual interest rate by 12 and multiply the term by 12.

For an example, suppose that with the earlier investment, you want to deposit $15 per month. Here's the revised formula to handle monthly deposits:

=FV(.05 / 12, 10 * 12, 15, -1000)

Calculating present value in Excel

Investors use the concept of present value to recognize the time value of money. Because an investor can receive interest, $1,000 today is worth less than $1,000 ten years from today. For example, $1,000 invested today at 10 percent interest per year, compounded annually, would return $2,593.74. Therefore, the present value of $2,593.74 at 10 percent, compounded annually, for 10 years is $1,000. Or, worded differently, $1,000 today is worth $2,593.74 ten years from today.

To find the present value, you can use the Excel PV function, which takes five arguments:

PV(<em>rate</em>, <em>nper</em>, <em>pmt</em>[, <em>fv</em>][, <em>type</em>])

The <em>rate</em> argument is the interest rate; <em>nper</em</em>> is the number of periods in the term; <em>pmt</em> is the amount of each payment; the optional <em>fv</em> argument is the future value you're trying to find the present value of; and the optional <em>type</em> argument is a number indicating when payments are made (0 or blank for end of period; 1 for beginning of period).

For example, the following formula calculates the present value of $2,593.74, the final value of an investment that returns 10 percent interest, compounded annually, for 10 years:

=PV(0.1, 10, 0, 2593.74)

When you're working with the PV function, negative numbers are cash outflows and positive numbers are cash inflows. Enter a negative number when making a payment; enter a positive number when receiving cash.

Present value also applies to loans and mortgages. The money you receive when you take out a loan is the present value of the loan. When calculating present value, be careful what you enter in the <em>rate</em</em>> and <em>nper</em> arguments. You must divide the annual interest rate by the number of payments per year. For example, if payments are monthly, you should divide the annual interest rate by 12. You must also multiply the term by the number of payments. For example, if payments are monthly, multiply the term by 12.

For example, if you'll be making monthly $15 deposits to the preceding investment, here’s the formula to calculate the revised present value of the investment:

=PV(0.1 / 12, 10 * 12, 15, 2593.74)

Determining loan payments in Excel

When borrowing money, whether for a mortgage, car financing, a student loan, or something else, the most basic analysis is to calculate the regular payment you must make to repay the loan. You use the Excel PMT function to determine the payment.

The PMT function takes three required arguments and two optional ones:

PMT(<em>rate</em>, <em>nper</em>, <em>pv</em>[, <em>fv</em>][, <em>type</em>])

The required arguments are <em>rate</em>, the fixed rate of interest over the term of the loan; <em>nper</em>, the number of payments over the term of the loan; and <em>pv</em>, the loan principal. The two optional arguments are <em>fv</em>, the future value of the loan, which is usually an end-of-loan balloon payment; and <em>type</em>, the type of payment: 0 (the default) for end-of-period payments or 1 for beginning-of-period payments.

A balloon payment covers any unpaid principal that remains at the end of a loan.

The following example calculates the monthly payment on a 3-percent, 25-year $200,000 mortgage:

=PMT(0.03 / 12, 25 * 12, 200000)

Note that the result of this formula is –948.42. Why the minus sign? The PMT function returns a negative value because a loan payment is money that you pay out.

As shown in the preceding formula, if the interest rate is annual, you can divide it by 12 to get the monthly rate; if the term is expressed in years, you can multiply it by 12 to get the number of months in the term.

With many loans, the payments take care of only a portion of the principal, with the remainder due as an end-of-loan balloon payment. This payment is the future value of the loan, so you enter it into the PMT function as the <em>fv</em> argument. You might think that the <em>pv</em> argument should therefore be the partial principal — that is, the original loan principal minus the balloon amount — because the loan term is designed to pay off only the partial principal. Nope. In a balloon loan, you also pay interest on the balloon part of the principal. Therefore, the PMT function's <em>pv</em> argument must be the entire principal, with the balloon portion as the (negative) <em>fv</em> argument.

Calculating a loan payment's principal and interest in Excel

It’s one thing to know the total amount for a regular loan payment, but breaking down a loan payment into its principal and interest components is often handy. The principal part is the amount of the loan payment that goes toward paying down the original loan amount, whereas the rest of the payment is the interest you’re shelling out to the lender.

To calculate loan payment principal and interest, you can use the PPMT and IPMT functions, respectively. As the loan progresses, the value of PPMT increases while the value of IPMT decreases, but the sum of the two is constant in each period and is equal to the loan payment.

Both functions take the same six arguments:

PPMT(<em>rate</em>, <em>per</em>, <em>nper</em>, <em>pv</em>[, <em>fv</em>][, <em>type</em>])
IPMT(<em>rate</em>, <em>per</em>, <em>nper</em>, <em>pv</em>[, <em>fv</em>][, <em>type</em>])

The four required arguments are <em>rate</em>, the fixed rate of interest over the loan term; <em>per</em>, the number of the payment period; <em>nper</em>, the number of payments over the term of the loan; and <em>pv</em>, the loan principal. The two optional arguments are <em>fv</em>, the future value of the loan; and <em>type</em>, the type of payment: 0 for end of period or 1 for beginning of period.

For example, the following two formulas calculate the principal and interest portions of the first monthly payment on a --percent, 25-year $200,000 mortgage:

=PPMT(0.03 / 12, 1, 25 * 12, 200000)
=IPMT(0.03 / 12, 1, 25 * 12, 200000)

Calculating cumulative loan principal and interest in Excel

To calculate how much principal or interest has accumulated between two periods of a loan, use the CUMPRINC or the CUMIPMT function, respectively. Both functions require the same six arguments:

CUMPRINC(<em>rate</em>, <em>nper</em>, <em>pv</em>, <em>start_period</em>, <em>end_period</em>, <em>type</em>])
CUMIPMT(<em>rate</em>, <em>nper</em>, <em>pv</em>, <em>start_period</em>, <em>end_period</em>, <em>type</em>])

Here, <em>rate</em> is the fixed rate of interest over the term of the loan; <em>nper</em> is the number of payments over the term of the loan; <em>pv</em> is the loan principal; <em>start_period</em> is the first period to include in the calculation; <em>end_period</em> is the last period to include in the calculation; and <em>type</em> is the type of payment: 0 for end of period or 1 for beginning of period.

For example, to find the cumulative principal or interest in the first year of a loan, set <em>start_period</em> to 1 and <em>end_period</em> to 12, as shown here:

CUMPRINC(0.03 / 12, 25 * 12, 200000, 1, 12, 0)
CUMIPMT(0.03 / 12, 25 * 12, 200000, 1, 12, 0)

For the second year, you'd set <em>start_period</em> to 13 and <em>end_period</em> to 24, and so on.

Finding the required interest rate in Excel

If you know how much you want to borrow, how long a term you want, and what payments you can afford, you can calculate what interest rate will satisfy these parameters using the Excel RATE function. For example, you can use this calculation to put off borrowing money if current interest rates are higher than the value you calculate.

The RATE function takes the following arguments:

RATE(<em>nper</em>, <em>pmt</em>, <em>pv</em>[, <em>fv</em>][, <em>type</em>][, <em>guess</em>])

The three required arguments are <em>nper</em>, the number of payments over the term of the loan; <em>pmt</em>, the periodic payment; and <em>pv</em>, the loan principal. RATE can also take three optional arguments: <em>fv</em>, the future value of the loan (the end-of-loan balloon payment); <em>type</em>, the type of payment (0 for end of period or 1 for beginning of period); and <em>guess</em>, a percentage value that Excel uses as a starting point for calculating the interest rate.

If you want an annual interest rate, you must divide the term by 12 if it is currently expressed in months. Conversely, if you have a monthly payment and you want an annual interest rate, you must multiply the payment by 12.

RATE uses an iterative process in which Excel starts with an initial guess value and attempts to refine each subsequent result to obtain the answer. If you omit <em>guess</em>, Excel uses a default value of 10 percent. If after 20 tries Excel can't come up with a value, it returns a #NUM! error. If that happens, you should enter a <em>guess</em> value and try again.

On a related note, if you know the principal, the interest rate, and the payment, you can calculate the length of the loan by using the NPER function:

NPER(<em>rate</em>, <em>pmt</em>, <em>pv</em>[, <em>fv</em>][, <em>type</em>])

The NPER function's three required arguments are <em>rate</em>, the fixed rate of interest; <em>pmt</em>, the loan payment; and <em>pv</em>, the loan principal. The two optional arguments are <em>fv</em>, the future value of the loan, and <em>type</em>, the type of payment (0 or 1).

Determining the internal rate of return in Excel

The internal rate of return is related to the net present value, which is the sum of a series of net cash flows, each of which has been discounted to the present using a fixed discount rate. The internal rate of return can be defined as the discount rate required to get a net present value of $0.

You can use the Excel IRR function to calculate the internal rate of return on an investment. The investment's cash flows don't have to be equal, but they must occur at regular intervals. IRR tells you the interest rate you receive on the investment. Here’s the syntax:

IRR(<em>values</em>[, <em>guess</em>])

The values argument is required and represents the range of cash flows over the term of the investment. It must contain at least one positive and one negative value. The <em>guess</em> argument is optional and specifies an initial estimate for the Excel iterative calculation of the internal rate of return (the default is 0.1). If after 20 tries Excel can't calculate a value, it returns a #NUM! error. If you see that error, enter a value for the <em>guess</em> argument and try again.

For example, given a series of cash flows in the range B3:G3, here's a formula that returns the internal rate of return using an initial guess of 0.11:

=IRR(B3:G3, 0.11)

You can use the NPV function to calculate the net present value of future cash flows. If all the cash flows are the same, you can use PV to calculate the present value. But when you have a series of varying cash flows, use NPV, which requires two arguments: <em>rate</em>, the discount rate over the term of the asset or investment, and <em>values</em>, the range of cash flows.

Calculating straight-line depreciation in Excel

The straight-line method of depreciation allocates depreciation evenly over the useful life of an asset. Salvage value is the value of an asset after its useful life has expired. To calculate straight-line depreciation, you take the cost of the asset, subtract any salvage value, and then divide by the useful life of the asset. The result is the amount of depreciation allocated to each period.

To calculate straight-line depreciation, you can use the Excel SLN function:

SLN(<em>cost</em>, <em>salvage</em>, <em>life</em>)

SLN takes three arguments: <em>cost</em>, the initial cost of the asset; <em>salvage</em>, the salvage value of the asset; and <em>life</em>, the life of the asset in periods. If you purchase an asset mid-year, you can calculate depreciation in months instead of years.

For example, if an equipment purchase was $8,500, the equipment's salvage value is $500, and the equipment’s useful life is four years, the following formula returns the annual straight-line depreciation:

=SLN(8500, 500, 4)

The carrying value is the cost of an asset minus the total depreciation taken to date. For example, the depreciation for an asset with a cost of $8,500, a salvage value of $500, and a useful life of four years would be allocated as follows:

Returning the fixed-declining balance depreciation in Excel

When calculating depreciation, accountants try to match the cost of an asset with the revenue it produces. Some assets produce more in earlier years than in later years. For those assets, accountants use accelerated methods of depreciation, which take more depreciation in the earlier years than in the later years. Fixed-declining balance is an accelerated method of depreciation.

To calculate fixed-declining balance depreciation, you can use the Excel DB function:

DB(<em>cost</em>, <em>salvage</em>, <em>life</em>, <em>period</em>[, <em>month</em>])

The DB function takes five arguments: <em>cost</em>, the cost of the asset; <em>salvage</em>, the salvage value; <em>life</em>, the useful life; <em>period</em>, the period for which you're calculating depreciation; and the optional <em>month</em>, the number of months in the first year. If you leave <em>month</em> blank, Excel uses a default value of 12.

For example, if an equipment purchase was $8,500, the equipment's salvage value is $500, and the equipment’s useful life is four years, the following formula returns the depreciation amount for the first year:

=DB(8500, 500, 4, 1)

The fixed-declining balance method of depreciation depreciates an asset with a cost of $8,500, a salvage value of $500, and a useful life of four years, as follows: ^* Amount adjusted for rounding error.

Determining the double-declining balance depreciation

Double-declining balance is an accelerated depreciation method that takes the rate you would apply by using straight-line depreciation, doubles it, and then applies the doubled rate to the carrying value of the asset.

To determine the double-declining balance depreciation, you can use the Excel DDB function

DDB(<em>cost</em>, <em>salvage</em>, <em>life</em>, <em>period</em>[, <em>factor</em>])

The DDB function takes five arguments: <em>cost</em>, the cost of the asset; <em>salvage</em>, the salvage value; <em>life</em>, the useful life; <em>period</em>, the period for which you're calculating depreciation; and the optional <em>factor</em>, the rate at which the balance declines. The default value for <em>factor</em> is 2, but to use a value other than twice the straight-line rate, you can enter the factor you want to use, such as 1.5 for a rate of 150 percent.

For example, if an equipment purchase was $8,500, the equipment’s salvage value is $500, and the equipment’s useful life is four years, the following formula returns the depreciation amount for the first year:

=DDB(8500, 500, 4, 1, 2)

The double-declining balance method of depreciation depreciates an asset with a cost of $8,500, a salvage value of $1,500, and a useful life of four years, as follows: ^* The DDB function does not depreciate the asset below the salvage value.