LESSON Using Basic Financial and Logical Functions

[Pages:16]L08620946 12/15/03 5:09 PM Page 187

LESSON

8

187

Using Basic Financial and Logical Functions

After completing this lesson, you will be able to: Use the PMT function to forecast loan payments. Calculate cumulative interest. Compute investment value. Use the IF function.

KEY TERMS

conditional formula cumulative interest future value

logical_test present value principal

Microsoft Excel is an invaluable tool for performing financial calculations. Using basic functions, you can easily calculate the monthly payments for a loan, figure the accrued value of an investment, and set the value of a cell by comparing the values of two other cells. With advanced financial functions, you can figure the rate of return on an investment, amortize a loan or mortgage over time, and track the depreciation of an asset.

In this lesson, you will use the PMT function to calculate loan payments, compute the cumulative interest paid on a loan using the CUMIPMT function, and figure the future value of a periodic investment with the FV function. Finally, you will compare investment options using the IF function.

At Adventure Works, the chief financial officer (CFO) is weighing the options for financing the upcoming chalet renovation. The mortgage broker offered two possible loan scenarios, but the financial adviser suggested some short-term investments. Using Excel's financial and logical functions, the CFO can calculate payment amounts, cumulative interest paid, and future value to determine the most cost-effective financing option.

IMPORTANT

Before you can use the practice files in this lesson, you must install them from the book's companion CD to their default location. For additional information on how to find and open files used in this book, see the "Using the CD-ROM" section at the beginning of this book.

L08620946 12/15/03 5:09 PM Page 188

188 Lesson 8 Using Basic Financial and Logical Functions

Using the PMT Function to Forecast Loan Payments

THE BOTTOM LINE

The PMT function calculates the payment required to repay a loan for a specified number of periods at a set interest rate. This function is often used to help determine how much you can afford to borrow, primarily for big-ticket items such as cars and homes.

The PMT (payment) function calculates payments for a loan based on a series of constant payments and a constant interest rate. Thus, it returns the borrower's required payments for a loan. For example, you want to buy a $20,000 car. You've saved $2,000 to put toward the purchase price, and you need to borrow $18,000. Your lender has given you an interest rate of 9 percent, and you will repay the loan in 48 months. Using the PMT function, after you plug in these numbers, your monthly payment for the 48 months is instantaneously calculated.

The PMT function requires the following syntax:

PMT(rate,nper,pv,fv,type)

The following table explains the meaning of each argument:

Argument Rate

Nper

Pv Fv Type

Explanation

The interest rate per payment period; when you are calculating monthly payments, divide the annual interest rate by 12; when calculating semimonthly payments, divide the annual rate by 24 The total number of loan payments; when the loan is issued for a number of years and requires monthly payments, you can enter the number of payments as 12* The present value (principal) of the loan The value of the loan after all payments are made; in general, this value is zero, and if this variable is omitted, it is assumed to be zero The timing of the loan payments; when the loan payment is due at the end of the payment period, use the default value of 0; when payment is due at the beginning of the payment period, set this value to 1

The Adventure Works CFO has narrowed her choices to two possible loans. Using the payment function, she determines the monthly payment for each loan based on the term and quoted interest rate.

To complete the procedures in this lesson, you must use the file

Financing in the Lesson08 folder in the Excel Core Practice folder located on your hard disk.

Open Financing from the Excel Core Practice/Lesson08 folder.

L08620946 12/15/03 5:09 PM Page 189

Lesson 8 Using Basic Financial and Logical Functions 189

Use the PMT function

In this exercise, you use the PMT function to calculate loan payments.

1 On the Loans sheet, click cell B10, and then click the Insert Function

button on the Standard toolbar. The Insert Function dialog box appears.

FIGURE 8-1

Insert Function dialog box

ANOTHER METHOD Open the Insert menu, and select Function. Click the Insert Function button on the Formula bar.

2 In the Or Select A Category list, click Financial.

The Select A Function list displays the available financial functions.

3 In the Select A Function list, scroll down, and click PMT. Then click OK.

The Function Arguments dialog box appears.

FIGURE 8-2

Function Arguments dialog box for the PMT function

L08620946 12/15/03 5:09 PM Page 190

190 Lesson 8 Using Basic Financial and Logical Functions

4 In the Rate box, click the Collapse Dialog button, and click cell B6. 5 Click the Expand Dialog button.

ANOTHER METHOD You can add cell addresses to arguments by typing them directly in the appropriate boxes of the Function Arguments dialog box. Or you can drag the Function Arguments dialog box out of the way, if necessary; click a cell or range to make a selection; and press Enter.

6 In the Rate box, to the right of B6, type /12.

You must divide the annual interest rate by 12 when calculating a monthly payment.

7 In the Nper box, click the Collapse Dialog button, click cell B8, and

press Enter or click the Expand Dialog button.

8 In the Pv box, click the Collapse Dialog button, click cell B4, and

press Enter or click the Expand Dialog button.

IMPORTANT Because payments are assumed to be outgoing, the result of the PMT function is a negative value.

FIGURE 8-3

Completing the PMT arguments

Result is a negative number because payments are outgoing

9 Click OK.

The monthly payment of (negative) $4,354.88 appears in cell B10.

10 Copy the formula in cell B10 to cell C10.

The monthly payment of (negative) $3,485.89 appears in cell C10.

L08620946 12/15/03 5:09 PM Page 191

Lesson 8 Using Basic Financial and Logical Functions 191

FIGURE 8-4

Copying the function

Save the workbook with the current name, and leave the file open for

the next exercise.

QUICK CHECK

Q. What is the meaning of the Nper argument in the PMT function?

A: Nper is the total number of loan payments.

QUICK REFERENCE

Use the Payment (PMT) function

1 Click the cell that will contain the formula. 2 Click the Insert Function button on the Standard toolbar. 3 Click Financial in the Or Select A Category list. 4 Click PMT in the Select A Function list, and click OK. 5 Enter the interest rate, number of payments, and principal. 6 Click OK.

Calculating Cumulative Interest

THE BOTTOM LINE

Determining the amount of interest that will be paid over the term of a loan can be an effective tool for comparing and evaluating loan options.

For tax and accounting purposes, it's often necessary to calculate the total amount of interest paid over a series of loan payments. Excel's CUMIPMT function performs this task for you. The CUMIPMT function can also be used to compare loan options. For example, you can determine how much you would save in interest paid by making a bigger down payment on the loan. Going back to the new car example, you could use the CUMIPMT function to figure out the interest you'll pay over the 48-month period at 9 percent interest if you put $4,000 down instead of $2,000.

L08620946 12/15/03 5:09 PM Page 192

192 Lesson 8 Using Basic Financial and Logical Functions

The CUMIPMT function requires the following syntax: CUMIPMT(rate,nper,pv,start_period,end_period,type)

The following table explains the meaning of each argument.

Argument Rate

Nper

Pv Start_period End_period

Type

Explanation

The interest rate per payment period; when you are calculating monthly payments, divide the annual interest rate by 12; when calculating semimonthly payments, divide the annual rate by 24 The total number of loan payments; when the loan is issued for a number of years and requires monthly payments, you can enter the number of payments as 12* The present value (principal) of the loan The first payment period in the calculation; the first period in a series of payments is numbered 1; the calculation can start with any period The last payment period in the calculation; this value can be any value greater than the Start_period; the value of the final period in a series is equal to the total number of payments The timing of the loan payments; when the loan payment is due at the end of the payment period, use the default value of 0; when payment is due at the beginning of the payment period, set this value to 1

IMPORTANT The CUMIPMT function is part of the Excel Analysis Toolpak. If the Analysis Toolpak is installed on your computer, it will appear on the list of Add-Ins accessible from the Tools menu. If the Analysis Toolpak is not installed, install it from the Microsoft Office 2003 or Microsoft Excel 2003 installation CD before continuing.

At Adventure Works, the CFO wants to compare the total amount of interest paid for each of the loans offered.

Use the CUMIPMT function

In this exercise, you activate the Analysis Toolpak and use the CUMIPMT function to calculate total interest paid for a loan.

1 On the Tools menu, click Add-Ins.

The Add-Ins dialog box appears.

L08620946 12/15/03 5:09 PM Page 193

Lesson 8 Using Basic Financial and Logical Functions 193

FIGURE 8-5

Add-Ins dialog box

TROUBLESHOOTING

Your list of available add-ins may vary from what is shown in Figure 8-5.

2 On the Add-Ins Available list, select the Analysis Toolpak check box,

and click OK.

Excel activates the Analysis Toolpak. Its functions are now available.

3 Click cell B12, and click the Insert Function button.

The Insert Function dialog box appears.

4 In the Or Select A Category list, click Financial, if necessary.

The Select A Function list displays the available financial functions.

5 In the Select A Function list, click CUMIPMT, and click OK.

The Function Arguments dialog box appears.

6 Click in the Rate box, and type B6/12. 7 Click in the Nper box, and type B8. 8 Click in the Pv box, and type B4. 9 In the Start_period box, type 1. 10 In the End_period box, type 48.

This formula will calculate the cumulative interest paid over the time between the given start and end periods. In this case, you are calculating cumulative interest for the entire life of the loan, so you begin with the first payment period (1) and end with the final payment period (48).

11 Scroll down in the Function Arguments dialog box, and in the Type

box, type 0.

Excel will assume that each payment is due at the end of its payment period.

L08620946 12/15/03 5:09 PM Page 194

194 Lesson 8 Using Basic Financial and Logical Functions FIGURE 8-6 Arguments for the CUMIPMT function

12 Click OK.

The amount of interest paid over the life of loan Option A ($34,034.36) appears in cell B12.

13 Copy the formula in cell B12 to cell C12.

The amount of interest paid over the life of loan Option B ($32,555.86) appears in cell C12.

FIGURE 8-7

Calculation of cumulative interest

QUICK CHECK

Q. What is the difference between the PMT function and the CUMIPMT function?

A: The PMT function calculates the amount you must pay per period on a loan, whereas the CUMIPMT function calculates the interest you will pay over the term of the loan.

Save the workbook, and leave it open for the next exercise.

QUICK REFERENCE Use the CUMIPMT function

1 Click the cell that will contain the formula. 2 Click the Insert Function button. 3 Click Financial in the Or Select A Category list. 4 Click CUMIPMT in the Select A Function list, and click OK.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download