Q: Can you go over the Excel function to calculate the debt service constant?
A: Please reference the copy below of Shockproof Training’s Excel worksheet for calculating debt service constants. Highlighted in yellow are the cells that you populate in this Excel worksheet to calculate the debt service constant and to calculate the annual debt service.
In this example, the interest rate is 5.50%, the term is 30 years, and payments are monthly, so 30 years x 12 monthly payments equate to 360 monthly payments. The monthly payments are due at the end of the period. Based on the values entered in these cells, the debt service constant, in this instance, is 0.068135.
To calculate annual debt service, enter the same values used to compute the debt service constant. Then input the loan amount. The Excel spreadsheet multiples the debt service constant by the loan amount to arrive at an annual debt service of $235,065.
If you would like a copy of this Debt Service Constant Excel worksheet, please just email us at instructor@shockproof.com.