Useful Functions For Excel

Microsoft Excel has long been a staple in the business world.  Its flexibility allows us to use it for spreadsheets, downloading data from other software, bank reconciliations, storing, sorting and managing data, and creating lists.However, Excel also has some useful and underused functions.

 Some of favorite underused functions of Excel are the PMT function, the NPV, and the IRR function.

 PMT Function

 You can calculate the payment amount for a loan, such as a car loan, using the PMT function. The PMT function requires that you know the loan principal, the number of payments, and the interest rate. The syntax of the PMT function is:

 PMT (rate,nper,pv)

Suppose you purchase a car for $35,000 making monthly payments for 5 years at an annual interest rate of 7.5%.  If the data for this example is stored in cells E18 – E20 respectively, the payment can be calculated with this formula/keystroke entry: =PMT(E18/12,E19,E20)

This entry yields a payment amount of $701.33 

NPV Function

If you want to compare the value of a dollar today to the value of the same dollar in the future taking inflation and returns into account, then Excel can help you calculate the net present value. The net present value is the current value of an investment based on a series of cash flows – original investment and income on that investment – and a discount rate. You can use the NPV function to calculate the net present value of an investment and its related cash flows. The syntax for the NPV function is :

NPV(rate,value1,value2…)

The rate argument is the rate for one time period, for example, an annual discount rate. The value1, value2, and so forth to a maximum of 29 arguments represent the original investment and income values.

Suppose you want to determine the net present for an original investment of $1,500 and the following periodic returns: $600, $500, $600, $1,000, and $1,200. In this example all cash flows are assumed to occur at the end of the time period. If the data is stored in cells D26 –D32 respectively, the NPV can be calculated using these key strokes: =NPV(D26, D27:D32) which yields a net present value of $837.12.

IRR Function

If you want to evaluate measure or compare the profitability of projects, an internal rate of return can be a tool for this analysis. The internal rate of return is an interest rate calculation for a series of cash flows that occur at regular intervals. Suppose you want to determine the IRR for a set of cash flows over a 6-year period. You can use the IRR function to do this. The syntax for the IRR function is:

IRR(values,guess)

The values argument represents the cash flows and the guess argument is your estimation or guess rate for the rate of return. Excel uses your guess rate as its starting point and can make as many as 20 cycles through the calculation. If you omit a guess rate, Excel begins with 10 percent.

If you assume a 1st year outflow of -4,000, and 2nd through 6th year inflows of $1,400, $1,500, $2,000, $1,750, and $2,500 with a guess rate of 15% and store that data in cells I18 through I24 respectively, your formula would look like this:

=IRR(118:I23,I24) yielding a internal rate of return of 32%.

 The PMT, NPV, and IRR calculations are just a few of the less common uses of Excel.

At Kasperek and Co. Accountants, we can help you explore how these tools and others will help you in your business.

By: Peggy Cavaness, CPA