Mrinmoy Roy
The Current Portion of Long-Term Debt is the amount of a company’s long-term debt that is due within the current Fiscal Year. This portion of the debt must be paid off within 12 months, so it is considered a current liability. The current portion of long-term debt is important to bondholders and creditors because it shows how much of the company’s debt will need to be paid in the near future. In this article, you will learn to calculate the Current Portion of Long-Term Debt in Excel. So, without having any further discussion, let’s dive straight into the topic.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
What Is Current Portion of Long-Term Debt?
The Current Portion of Long-Term Debt is the amount of a company’s long-term debt that is due within the current fiscal year. This amount is typically reported as a current liability on a company’s balance sheet.
The Current Portion of Long-Term Debt is important to bondholders and other creditors because it represents the amount of debt that must be paid in the near future. This amount can also be used to calculate a company’s debt-to-equity ratio, which is a measure of a company’s financial leverage.
Some companies choose to pay off the Current Portion of Long-Term Debt early, which can save on interest payments. This strategy is typically used when a company has excess cash on hand and wants to reduce its debt-to-equity ratio.
What Is Loan Amortization Schedule?
A Loan Amortization Schedule is a table that shows you how much of your loan principal and interest you will pay off each month until the loan is paid in full. The schedule also shows you the remaining balance of your loan after each payment is made.
Making regular payments on time is important because it allows you to pay off your loan more quickly and avoid paying extra interest. To make sure you stay on track, consider setting up automatic payments from your bank account.
A loan amortization schedule is a great tool for helping you budget and plan for the future. It can also help you keep track of your progress as you work towards paying off your loan.
Steps to Calculate Current Portion of Long Term Debt in Excel
Consider the following scenario for which you want to calculate the Current Portion of Long-Term Debt.
- Annual Interest Rate 8%.
- Total Number of Years 2.
- Periods per Year 12.
- Balance $500,000.
To calculate the Current Portion of Long-Term Debt we need to create a loan amortization schedule first. Using the schedule we can calculate the Current Portion of Long-Term Debt quite easily.
Step-1: Using PMT Function to Calculate Payment Amount
At first, we will calculate the payment amount for each period throughout the total time span. To calculate the monthly payment amount, I will use the PMT function.
Now, follow the steps below to calculate the monthly payment amount.
❶ First insert the following formula in cell C13.
=-PMT($D$4/$D$6,COUNT(B13:$B$36),F12)❷ After that, press ENTER.
❸ Now double-click on the bottom-right corner of cell C13. It will auto-fill the formula to the entire Payment column.
Formula Breakdown
- $D$4 refers to the Annual Interest Rate.
- $D$6 refers to the Periods per Year.
- COUNT(B13:$B$36) Here, the COUNT function returns the remains number of periods against the loan.
- F12 refers to the total loan balance remaining.
- =-PMT($D$4/$D$6,COUNT(B13:$B$36),F12) calculates the payment amount. Originally the function returns a negative value. Thus the minus sign (-) before the function will return a positive value.
Step-2: Calculating Amount of Interest Paid Each Period
After calculating the payment amount for each period, you need to calculate the amount of Interest Paid for each period.
To do that,
❶ Insert the following formula in cell D13.
=F12*$D$4/$D$6❷ After that, press ENTER.
❸ Now double-click on the bottom-right corner of cell D13. It will auto-fill the formula to the entire Interest Paid column.
Formula Breakdown
- F12 is the remaining balance you need to pay back.
- $D$4 is the Annual Interest Rate.
- $D$6 refers to the Periods per Year.
Read More: How to Calculate Annual Debt Service in Excel (3 Ideal Examples)
Step-3: Estimating Amount of Principal Paid Each Period
After calculating the Interest, you need to calculate the amount of Principal Paid for each period.
To do that,
❶ Insert the following formula in cell E13.
=C13-D13❷ After that, press ENTER.
❸ Now double-click on the bottom-right corner of cell E13. It will auto-fill the formula to the entire Principal Paid column.
Formula Breakdown
- C13 refers to the Payment
- D13 refers to the amount of Interest Paid.
Read More: Debt Service Coverage Ratio Formula in Excel
Step-4: Figuring Out the Remaining Original Balance
Now, you need to calculate the amount of the remaining balance.
For that,
❶ Insert the following formula in cell F13.
=F12-E13❷ After that, press ENTER.
❸ Now double-click on the bottom-right corner of cell F13. It will auto-fill the formula to the entire Original Balance column.
Formula Breakdown
- F12 refers to the previous value of the remaining balance to pay back.
- E13 is the amount of Principal Paid.
Read More: How to Calculate Cost of Debt in Excel (3 Simple Ways)
Step-5: Finding Out the Current Portion of Long-Term Debt
Let’s say, you want to create a balance sheet in the month of December 2022.
At this point, you can calculate the Current Portion of Long-Term Debt simply by summing up the Principal Paid amounts for the entire year 2023.
This means you need to sum the cells E15 to E26 to calculate the Current Portion of Long-Term Debt.
To calculate the Current Portion of Long-Term Debt,
❶ Insert the following formula in cell F9.
=SUM(E15:E26)❷ After that, press the ENTER button.
Now you will get the Current Portion of Long-Term Debt in cell F9 which is $243,249.64.
Formula Breakdown
- The range E15:E26 refers to the cell ranges for the period of the year 2023.
Practice Section
You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.
Conclusion
To sum up, I have discussed steps to calculate the current portion of long-term debt in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
