Photo Credit: Gnerk
As we’ve explored in previous posts, Leverage can be a powerful tool to magnify returns, but as a mentor of mine always says: never let financing drive value in a deal. In commercial real estate analysis, it’s important to analyze the impact of financing, and a widely available tool, Excel, can help you do this.
The purpose of this Blog is to teach you the application within Excel. We strongly suggest that before you continue reading, download the sample spreadsheet by clicking here. It includes practical application, examples, and a prove-you-know-it section. Did I mention you should download the sample spreadsheet before continuing?
Click Here: Sample Spreadsheet
Now that you’ve downloaded your spreadsheet, let’s dive right in and understand an important part of any commercial real estate analysis: debt service calculation.
If you’re thinking, wait, that’s easy, just use the PMT formula. Yes, you’re right, but you’re obviously not very interested in an extra $702.85.
PMT calculates the payment for a loan given constant payments and a constant interest rate.
Using the following PMT example of a $1,000,000 loan at a 6% interest rate, amortized over 30 years:
=PMT(rate, amortization period, loan amount) -> =PMT(6%,30,1000000)
Excel calculates an annual debt service of ($72,648.91)
So here’s the issue: what if the loan docs state payments should be calculated on a 30/360 schedule?
… who reads the loan docs?
Here’s the technical:
Excel’s built-in PMT calculation calls for the borrower to pay a yearly sum payment (so we don’t really care how many days are in a year).
The 30/360 method assumes every month has 30 day, each year has 360 days, and the borrower pays 12 equal payments (Wouldn’t fly with the Mayans, but hey, makes calculations easy).
So how do we calculate debt service in Excel for a 30/360 loan?
=PMT(rate/12,amortization period*12,loan amount)*12 -> =PMT(6%/12,30*12,1000000)*12
Excel calculates an annual debt service of ($71,946.06), a difference of $702.85, a nice bonus for those who know the system, and it makes sense: pay off the principle faster, less interest to accumulate.
Application: Would you pay $702.85 to hold off your creditors until the end of the year? You might, and that, my friends, is the time value of money.
Join us by subscribing for free email updates and follow us on Twitter.