Calculating interest and principal components of loan repayments

2m 6s

 Welcome

1m 8s

 Disclaimer

26s


13m 20s

18m 40s

18m 48s

8m 14s

6m 20s

6m 12s

10m 58s

13m 32s

7m 30s

22m 28s

 Introducing amortization

2m 20s

 Introducing depreciation

2m 1s


9m 34s

23s

 Goodbye

23s

Numbers and financial data drives today's business world and Excel 2007: Financial Analysis can help decode this information. The proper understanding of these numbers, and the formulas behind them, can be the gateway to corporate and personal success. Microsoft MVP (Most Valuable Professional) Curt Frye teaches basic fluency in corporate finance, enabling users to see the meaning behind essential financial calculations. Curt explains how to review formulas to ensure they have the proper inputs, and shows how to interpret formula output. He also covers how to calculate leverage ratios and amortization and depreciation schedules, as well as forecast future growth. Exercise files accompany this course.
 Building a financial worksheet with Pivot Tables Reviewing financial statements through commonsized balance sheets Calculating percentage change over time in financial statements Determining profitability ratios and return on investments Studying liquidity and activity ratios through an average collection period Computing the future value of an investment
Calculating interest and principal components of loan repayments
Calculating the amount of principal you've paid on a loan enables you to determine the amount of equity you have in a purchase. Your equity is your down payment, plus any loan principal you've paid, plus the asset's appreciation, if any, and also what your new monthly payment would be if you refinance the loan. Calculating the interest paid on the loan enables you to find the amount of interest you've paid in the fiscal year, which you can often write off in your tax return. You can determine the amount of interest included in a particular loan payment using the IPMT function.
IPMT simply stands for Interest Payment. Start the IPMT function and you have six arguments. The rate, the number of the period for which you are calculating the interest, the number of periods in the entire loan, the present value of the loan, which is simply the principal, the future value of the loan, which is any amount that will be unpaid or that you will have paid in excess at the end of the loan's term, and also the type. Type is either zero or omitted, in which case you make your payments in arrears or at the end of a period, or one, in which you make your payments at the beginning of a period.
In almost every case, you will be making your payments at the end of the period, so you would leave that argument blank. So, in this case, we have the interest payment, we have the rate in B5, and because we're going to be copying this formula down the column in the table, we want to leave that cell static or keep it as an absolute reference, then divide by 12, so we have the proper rate as divided by the number of months, and then we have the period.
The period for this payment is in A12 and we can leave this Cell Reference as a relative reference, so it does change when we copy the formula down the column. The number of periods is the loan's term, in this case 30 years, multiplied by 12, which is the number of months in a year. So we have B7, again, we don't want this Cell Reference to change, so we make it an absolute reference by pressing F4, multiplied by 12. The present value of the loan is the principal. That's in cell B3.
Again, F4 to make it an absolute reference. We're paying off the entire loan and we're making our payment at the end of the period, so we can leave the future value and Type arguments blank. Hit Return and there we have the amount of interest paid in each of these loan payments. You'll notice that the values are in red, which means they're negative values representing a cash flow from you, an expense, in other words. We can do something similar for principal, to calculate the principal paid in each loan payment.
For that, we use the PPMT formula, and that is Principal Payment. It has the same arguments as the IPMT function. It's the rate, which is in B5, again, F4 to keep it constant, divide by 12, the period in A12, the number of periods is B7, absolute reference so it doesn't change, times 12, and then the present value of the loan, which is in cell B3.
F4 to make it an absolute reference. Close it, and there you have the principal paid in each of these 12 loan payments. You can also determine the cumulative interest and principal you've paid on a loan by using the CUMIPMT and CUMPRINC functions. The CUMIPMT and CUMPRINC functions require you to enter the periods for which you want to calculate interest and principal paid. For example, in this case, if you borrow $500,000 at 4.5% and you want to determine how much interest and principal you will pay over the first year, you can create these formulas.
For interest, you will create the formula =CUMIPMT, have the rate in B5, divide it by 12, the number of periods is the loan's term in B7, times 12, the present value of the loan, which is in the cell B3. The start period, which because we're looking at the first year of the loan is one, the first payment, and then the end period, which is 12. Again, we can leave Type blank, because we're paying in arrears.
So, we enter a type of zero and hit Return and you'll see that we'll have paid cumulative interest in the first year of $22334.99. If you want to do the same thing to calculate the amount of principal you've paid, you enter the CUMPRINC function. You have a rate in cell B5, divided by 12 for the number of payments during a year, the number of periods, which is the term, B7, times 12, the principal of the loan or the present value, which is in cell B3, the start period, again that's 1, the end period of 12, and the type, which is zero.
Close the parenthesis and you'll see that you've paid just over $8,066 against principal. I'll format that value as an accounting value, so you can see it as a currency value. Calculating the amount of principal and interest you have or will pay off on your loans reflects both the equity you have in your purchases and the amount of money you can write off on your taxes. Be sure to watch your interest expenses closely.
There are currently no FAQs about Excel 2007: Financial Analysis.