From the course: Excel: Financial Modeling with Dynamic Arrays

Unlock this course with a free trial

Join today to access over 25,200 courses taught by industry experts.

Building the debt schedule

Building the debt schedule - Microsoft Excel Tutorial

From the course: Excel: Financial Modeling with Dynamic Arrays

Start my 1-month free trial Buy for my team

Building the debt schedule

“

- [Instructor] Now we need to model the debt. So, just moving down the page here, the debt schedule has a very similar corkscrew layout, just like the depreciation schedule that we just built. So, let's start, first of all, with our principal repayment. We have our assumptions that have been pulled through from the Assumptions tab. So, we'll start first of all with our PPMT function, our rate is 7% divided by 12. We are in period one of a 10-year loan, multiplied by 12, and the loan amount is 30,000. Okay, let's have a look. We would expect that amount to be increasing over time, so just try it out and check your numbers against mine, so we know that principal amounts increase over the life of the loan. So, once we've got that working, we can calculate the interest component. Remember that the principal component will go on the balance sheet and the interest will drive the income statement. So, let's go in and calculate the IPMT, so the rate divided by 12, period one hash 10 years…

Contents