Ms Office Tune Up

Any loan payment has two components: principal repayment and interest charged. Interest charges are almost always front-loaded, which means that the interest component is highest at the beginning of the loan and gradually decreases with each payment. This means, con­versely, that the principal component increases gradually with each payment. How can you tell what the principal and interest components are for any given period of a loan? You can use the PPMT() and IPMT() functions, respectively:

PPMT(rate, per, nper, pv[, fv][, type])

IPMT(rate, per, nper, pv[, fv][, type])

Figure 1 shows a worksheet that applies these functions to a loan. The data table shows the principal (column E) and interest (column F) components of the loan for the first ten periods and for the final period. Note that with each period, the principal portion increases, and the interest portion decreases. However, the total remains the same (as confirmed by the Total column), which is as it should be because the payment remains constant through the life of the loan.

NOTE

When working with loan and investment formulas, always remember that amounts you pay out—such as loan payments and deposits to investments—are negative values, and amounts you receive—such as loan principal amounts and investment payouts—are positive values.

CAUTION

To ensure that your loan and investment models return accurate results, be sure to use consistent units when dealing with the interest and term. For example, if you have an annual interest rate and a term expressed in years and you want to calculate the monthly principal and interest, you need divide the interest rate by 12 and multiply the term by 12, as shown in Figure 1.

Figure 1 This worksheet uses the PPMT()and IPMT() functions to break out the principal and interest components of a loan payment.

When deciding whether to take out a mortgage, car loan, or other form of debt, it’s a good idea to know how much interest you’ll pay over the term of the loan. The easiest way to calculate this is to multiply the monthly payment by the number of payments to get the total cost of the loan and then subtract the principal:

=(Monthly Payment * Total Payments) -Principal

To calculate the loan payment, use the PMT() function: PMT(rate, nper, pv[, fv][, type])

In Figure 1, for example, given the annual interest rate (cell B2), the period in years (B3), and the loan principal (B4), here’s the formula in cell B5 that calculates the monthly payment:

=PMT(B2 / 12, B3 * 12, B4)

In Figure 2, I’ve added a Total Interest cell (B6) that uses the payment, period, and prin­cipal to calculate the total interest costs over the life of the loan.

Figure 2 Given the payment, period, and principal, you can calculate the total interest paid out during a loan.

There are many business scenarios where you need to know not only the principal and interest you pay each period and the total interest over the loan term, but also how much principal or interest you’ve paid up to a given period. For example, if you sign up for a mortgage with a five-year term, how much principal will you have paid off by the end of the term? Similarly, a business might need to know the total interest payments a loan requires in the first year so that it can factor the result into its expense budgeting.

You can solve these kinds of problems by using a couple of functions:

CUMPRINC(rate, nper, pv, start_period, end_period, type) CUMIPMT(rate, nper, pv, start_period, end_period, type)

CAUTION

In both CUMPRINC() and CUMIPMT(), all of the arguments are required. If you omit the type argument (which is optional in most other financial functions), Excel returns the #N/A error.

The main difference between CUMPRINC() and CUMIPMT() and PPMT() and IPMT() is the start_period and end_period arguments. For example, to find the cumulative principal or interest in the first year of a loan, you set start_period to 1 and end_period to 12; for the second year, you set start_period to 13 and end_period to 24. Here are a couple of formu­las that calculate these values for any year, assuming that the year value (1, 2, and so on) is in cell D2:

start_period: (D2 – 1) * 12 + 1

end_period: D2 * 12

In Figure 3, I’ve updated the loan model to include columns for the Cumulative Principal and Cumulative Interest.

Figure 3 This worksheet uses the CUMPRINC()and CUMIPMT()functions to return the cumulative principal and interest for several periods of a loan.

NOTE

Note that the CUMIPMT()function gives you an easier way to calculate the total interest costs for a loan. Set the start_periodto 1and the end_periodto the number of periods (the valueof nper), as shown in cell I12 in Figure 3. Note that this value is the same as the Total Interest calculation (cell B6).

Technorati Tags: Principal Interest Loan, excel 2007

Popularity: 4% [?]