applied tips : may 2008

microsoft excel : functions for finance

Speed through complex calculations with Excel's financial formulas.

Because Excel is a business-oriented program, it should come as no surprise that it features a category of Financial functions.

These functions can help you calculate the payment amount for a loan, determine the future value of an investment, or calculate depreciation on your assets—amongst other things.

To enter a function, you can simply type an “=” symbol and the function name and the required function arguments into the cell or formula bar. Or, you can get a little more assistance from Excel by clicking the Insert Function button (called the Paste Function button in Excel 97 and 2000), which opens the Insert Function (or Paste Function) dialog box, where you can search for a function. If you select the Financial category, all the financial functions are listed.

Excel has many financial functions—too many to cover them all here—but this article will introduce you to some of the most common ones. For more examples and descriptions regarding functions, check out Excel’s Help feature.

PMT function

Syntax: =PMT(rate,nper,pv,fv,type)

What it does: Calculates the payment amount for a loan based on constant payments and a constant interest rate.

Example: =PMT(6%/12,36,30000,0,0) – Calculates the payment amount on a $30,000 loan with a 6 percent annual interest rate, with monthly payments for 36 months, where the cash balance you want to attain after the last payment is 0 (fv) and the payments are due at the end of the month (type).

The PMT function calculates the amount of the payment on a loan, which includes both principal and interest. If the payments are due at the beginning of each period, instead of at the end as they are in the example, you would change the last argument from “0” to “1”.

Also, it’s important to make sure that you use consistent units when entering the rate and nper arguments. For example, if payments will be made monthly for two years, and the annual interest rate is 5 percent, then nper is 2*12 and the rate should also be stated in months—5 percent/12. If payments were yearly for two years, nper would be 2 and the rate would simply be 5 percent.

PV function

Syntax: =PV(rate,nper,pmt,fv,type)

What it does: Determines the total amount that a series of future payments is worth now.

Example: =PV(6%/12,36,300,0,0) – Calculates the present value of a future series of 36 monthly payments of $300 at a 6 percent yearly interest rate, where the cash balance you want to attain after the last payment is 0 (fv) and the payments are due at the end of the month (type).

The PV function is almost like a reverse of the PMT function. Instead of knowing the present value amount and finding the payment amount, you already know the payment amount and you want to find out the present value amount.

FV function

Syntax: =FV(rate,nper,pmt,pv,type)

What it does: Determines the future value of an investment based on periodic, constant payments and a constant interest rate.

Example: =FV(6%/12,36,-300,0,0) – Calculates the future value of 36 payments of $300 earning a constant annual interest rate of 6 percent.

The FV function is useful for showing you how much an investment will grow. For example, if you invest the same amount of money in a security that earns 6 percent per year, you can predict what the value of the investment will be after a certain period of time and number of payments.

Now let’s switch gears slightly and look at a couple financial functions that can help you calculate depreciation on your assets (assets are items with a future benefit such as equipment or furniture).

DDB function

Syntax: =DDB(cost,salvage,life,period,factor)

What it does: Returns the depreciation of an asset for a specified period using the double-declining balance (DDB) method or another method you specify.

Example: =DDB(5000,500,5,1,2) – Calculates the depreciation expense for an asset that cost $5,000, has a $500 salvage value, a 5 year life, was calculated for the first period (in this case the first year, using the same units as the life), and is declining at a rate of 2 (2 is divided by the 5 year life and multiplied by the remaining asset balance each period).

The DDB function assists you with a common way of calculating depreciation expense on your assets. The DDB method is an “accelerated” method of depreciation because a larger portion of depreciation expense is taken near the beginning of the asset’s life. 

In this example, if you want to find the depreciation for the second year, you can replace the fourth argument, “1”, with “2”. Also, you can change the rate of depreciation by changing the last argument. For example, you could change the factor from “2” to “1.5” to slow the depreciation rate. To determine the proper lives and rates to be used in the function, you need to refer to your company’s policy or IRS rules.

Another way to calculate depreciation is with the straight-line function (SLN).

SLN function

Syntax: =SLN(cost,salvage,life)

What it does: Returns the straight-line depreciation of an asset for a period.

Example: =SLN(5000,500,5) – Calculates the depreciation expense for an asset that cost $5,000, has a $500 salvage value and a 5 year life.

The SLN function differs from the DDB function in that it is not an “accelerated” method of depreciation, but rather the same amount of depreciation is taken for each year of an asset’s life.

Remember, these functions are just the beginning. Be sure to check out the rest of the functions in the financial category when you get a chance.  With Excel’s help, you too can become a financial guru!

Other useful functions

Here's a few more functions that might be useful in the world of finance:

For help with any of these functions, just type the name of the function into Excel's help search tool and you'll get explanations, examples, and more.

schedule a class on excel

Request an on-site Excel class from Applied Office. Sessions are inexpensive and your employees will be shown how to create spreadsheets, charts, and use formulas that will save them a lot of time.  Learn more here

upcoming classes on excel

Classes on Microsoft Excel are scheduled at University of the Pacific, and you can attend!  Review the Upcoming Classes for more information.

quick reference card

Get the Quick Reference Card on Microsoft Excel! Download it for free and print it on your own printer. You might even want to laminate it.