How to use some of Excel Amortization functions to determine the principal and interest paid during any periods of a loan.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 04:15
- - Skill Level: 5
- - What's my skill level
Excel Amortization functions
The CUMIPMT and CUMPRINC functions allow you to calculate the net interest paid and principal paid over any particular period without having to generate amortization tables
In this segment you will learn how to activate these functions and use these functions instead of building amortization tables.
Please note that you need to have the Analysis Toolpack loaded in order for these functions to work.
In this example we have a loan outstanding of 100000, with an interest rate of 11%, over a period of 5 years. Generating a repayment amount, and we have developed an amortization table, so that we can work out what the interest charge is each year, and what the actual capital repayment is each year. However, in Excel there’s and easier way to do that, using the Cumulative Interest Payment function and the Cumulative Principal Payment function.
If you wanted to work out the interest payments over the years 1 and 2, you can do the following
click on cell where you want the answer,
activate the Function Wizard
and find CUMIPMT which stands for Cumulative Interest Payment
and click ok,
and this will pop up, what it asks for is a bit of information about the loan,
so it asks for the rate, in this case is 11%,
the number of periods is the total number of payment periods, which in this case is 5,
the present value, is the outstanding amount on the loan, which is the 100000,
and this is the start period, when do you want to know where the interest payments start, and because we are looking for years 1 and 2 were going to start, in year 1
and the end period is year 2
and don’t forget if you go a little bit lower down, you need to tell it what type of payment method this is, is the payment happening at the end of the year, or the beginning of the year, and 0 indicates the end of year
and if we say ok,
what’ll come up is it’ll give you an answer, and if you compare that 20233, to this answer here where its exactly the same 20233, you’ll see you’ve got the correct interest amount,
and using this function you can maybe get the full interest charged, by using a full 5 year period,
you push enter and you’ll see you get the same interest amount as if you had done an amortization table,
you can do the same sort of thing with regards to the capital repayments or principal repayments.
lets ignore this for now and say we want to find out the principal repayments made during years 2 and 3
from our amortization table we know that the answer needs to be 37607,
if you click in this cell ,
activate the function wizard,
and go to the cum principle payment (CUMPRINC)
and click ok,
it asks you for details of the loan, it ask you for the rate which is the 11%,
the number of periods which is 5
present value or outstanding loan amount, 100000,
in this case we want the start period to be year 2
and the end period year 3,
and this loan is an end of period payment type, so 0,
we click ok,
what you’ll have is there’s your answer. For there years 2 and 3 its saying 37607
and if you take years 2 and 3 you’ll see 37607