#Excel campus how to use tables in excel how to
#Excel campus how to use tables in excel series

Then follow the same method to name the two Side Calculations cells. Then, to assign the first four range names to the cells in the yellow area, select the range B2:C5 in the Formulas, Defined Names group choose Create from Selection (or press Ctrl + Shift + F3) to launch the Create Names dialog make sure that only Left Column is checked then choose OK. The yellow area contains the values shown. To create this figure, first create the Settings and Side Calculation areas. This figure shows the complete setup to create an even-payment amortization table…

#Excel campus how to use tables in excel how to

Now let’s see how to create both amortization tables, and how to use both functions… Even-Payment Loans and Excel’s IPMT Function Notice that with the straight-line loan, total payments decline every period. The straight horizontal line shown for the principal repayments in the second chart illustrates the reason this is called a straight-line loan. The stacked area charts below plot the interest and principal for each type of loan. And the second table uses the straight-line method, where each amount in the principal ( Prin) column is identical. To illustrate these two repayment methods, the left amortization table below uses the even-payment method, where each amount in the payment ( Pmt) column is identical. (Because the “S” probably stands for straight-line, that’s what I’ll call this type of loan.) This type of loan is variously called “even principal”, “level debt service”, or “straight-line.” The ISPMT function returns the interest payment for a given period for this type of loan. On the other hand, some loans use a repayment schedule with even principal payments. The IPMT function returns the interest payment for a given period for this type of loan.

#Excel campus how to use tables in excel series

Or, because an annuity in this context is a series of equal payments at regular intervals, this type of loan for the purchase of real estate often is called an annuity mortgage.

This type of loan is called an even-payment loan. Most mortgages, car loans, and other amortizing loans in the US use a repayment schedule with identical periodic payments. Two Types of Time-Value-of-Money (TVM) Functions for Two Types of Loans

The second article in this series, How to Use Excel Formulas to Calculate Cumulative Term-Loan Interest Payments, uses the same amortization tables to explain how to calculate cumulative principal and interest for both even-payment and straight-line loans.

In this article, I’m going to explain both types of loans, show you how to create amortization tables for them, and then use those tables to illustrate the ISPMT and IPMT functions in action. But unfortunately, the two help topics for these functions don’t make it clear which type of loan each function supports. Microsoft recognizes those loan types by giving us the Excel functions ISPMT and IPMT, which tell us the amount of interest we’ll pay with each type of loan on a specific period. When you borrow money for a fixed period with periodic payments, you could have two types of loans: even-payment or straight-line.