Mashhura Jahan
The calculation of Interest During Construction is different from normal Interest. If you are looking for a way to calculate the interest during your construction period then this article will be helpful for you. The objective of this article is to explain the calculation of Interest During Construction in Excel.
Download Practice Workbook
You can download the practice workbook from here.
What Is Interest During Construction?
Interest During Construction is the interest that is applied in the construction period. It is calculated on the debt for the project. You will need to know the debt, the interest rate, and some other variables to calculate Interest During Construction.
Step by Step Calculation of Interest During Construction in Excel
Here, I have taken the following dataset to explain this article. The Start Date and the End Date of the construction period are given. The annual Interest Rate is 8%. And the Debt in Year 1 is $88,500 and in Year 2 is $90,550. Now, I will show you the calculation of the Interest During Construction in Excel. Let’s see the steps.
Step-01: Select Drawdown Mode and Frequency
In this first step, I will show you how you can select the Drawdown Mode and the Drawdown Frequency in a dynamic way.
- First, create a section for entering the Drawdown Mode and Drawdown Frequency.
- Next, select the cell where you want to show the Drawdown Mode. Here, I selected cell C12.
- Then, go to the Data tab.
- Afterward, select Data Validation.
- Consequently, the Data Validation dialog box will appear.
- Select the Settings tab.
- Then, Click on the drop-down button for Allow from Validation criteria.
- After that, select List.
- Then, write the options you want in the Source section.
- Finally, select OK.
- Next, you will see a drop-down button has been inserted in your selected cell which is cell C12.
- To select Drawdown Mode, firstly Click on the drop-down button.
- Then, select the mode you want. Here, I selected Quarterly.
- After that, you will see that Quarterly is showing as Drawdown Mode.
- Next, select the cell where you want to show the Drawdown Frequency. Here, I selected cell C13.
- Then, in cell C13 write the following formula.
=IF(C12="Annually",12,IF(C12="Half Yearly",6,3))
- Finally, press Enter and you will get your Drawdown Frequency.
🔎 How Does the Formula Work?
- IF(C12=”Half Yearly”,6,3): Here, the IF function returns 6 if the logical_test is True. Otherwise, it returns 3.
- IF(C12=”Annually”,12,IF(C12=”Half Yearly”,6,3)): Now, the IF function returns 12 if the logical_test is True. Otherwise, it will go to the next IF function.
Step-02: Create Table for Interest During Construction
Now, I will create a table where I will calculate everything I need for the Calculation of Interest During Construction in Excel.
- In the beginning, create a table with 4 columns.
- Then, name the columns as Date, Drawdown Amount, Cumulative Amount, and Interest.
Read More: How to Calculate Interest on a Loan in Excel (5 Methods)
Step-03: Insert Dates into Table
Here, I will insert the Dates for every drawdown period for the project.
- Firstly, select the cell where you want the first Date.
- Secondly, write the following formula in that selected cell.
=C4
- Thirdly, press Enter to get the Date.
- Afterward, select the cell where you want the second Date. Here, I selected cell B18.
- Then, in cell B18 write the following formula.
=IF(B17<$C$5,EDATE(B17,$C$13),"")
- Next, press Enter to get the Date.
- After that, drag the Fill Handle down to copy the formula to the other cells.
🔎 How Does the Formula Work?
- EDATE(B17,$C$13): In the EDATE function, I selected B17 as start_date and C13 as months. The formula will return the date after the months in cell C13 from the date in cell B17.
- IF(B17<$C$5,EDATE(B17,$C$13),””): Now, the IF function will check if the value in cell C5 is less than the value in cell C5. if the logical_test is True then the formula returns the Date generated by the EDATE function. Otherwise, it returns blank.
- Next, you can see that I have copied the formula to the other cells and got my desired output.
- In the following image, you can see that the last date is after the End Date. So, I will delete this date.
- Finally, this is how my table looks at this stage.
Read More: How to Calculate Interest Between Two Dates Excel (2 Easy Ways)
Similar Readings
- How to Calculate GPF Interest in Excel (with Easy Steps)
- Split Principal and Interest in EMI in Excel (with Easy Steps)
- How to Make HELOC Payment Calculator Using Principal and Interest in Excel
- Create CD Interest Calculator in Excel (2 Easy Methods)
- How to Perform Actual 360 Interest Calculation in Excel
Step-04: Calculate Drawdown Amount
In this step, I will calculate the Drawdown Amount for each period. The drawdown amount refers to the amount by which an investment is down from the peak before it reaches the peak.
- First, select the cell where you want to calculate the Drawdown Amount for Year 1.
- Then, write the following code in that selected cell.
=$C$9/(12/$C$13)
- Next, press Enter to get the result.
- Afterward, drag the Fill Handle down to copy the formula to 3 more cells.
- Finally, you can see that I have copied the formula and got the Drawdown Amount for Year 1.
- Next, select the cell where you want to calculate the Drawdown Amount for Year 2. Here, I selected cell C21.
- Then, in cell C21 write the following formula.
=$C$10/(12/$C$13)
- After that, press Enter.
- Further, drag the Fill Handle down to copy the formula to 3 more cells as the Drawdown Mode is Quarterly.
- Here, you can see that I have copied the formula.
Step-05: Calculate Cumulative Amount
Now, I have reached the 5th step of calculation of Interest During Construction in Excel. In this step, I will calculate the Cumulative Amount.
- Firstly, select the cell where you want the first Cumulative Amount. Here, I selected cell D17.
- Secondly, in cell D17 write the following formula.
=C17
- Thirdly, press Enter.
- After that, select the cell where you want to calculate the next Cumulative Amount.
- Next, write the following formula in that selected cell.
=C18+D17
- Then, press Enter.
- Afterward, drag the Fill Handle down to copy the formula to the other cells.
- Lastly, you can see that I have copied the formula to the other cells and got the Cumulative Amount.
Read More: How to Use Cumulative Interest Formula in Excel (3 Easy Ways)
Step-06: Determine Interest
In this step, I will show you how you can determine your Interest.
- In the beginning, select the cell where you want to calculate the Interest.
- Next, write the following formula in that selected cell.
=D17*$C$6*($C$13/12)
- After that, press Enter.
- Then, drag the Fill Handle down to copy the formula.
- Finally, you can see that I have copied the formula to the other cells and got the Interest.
Read More: How to Calculate Interest Rate in Excel (3 Ways)
Step-07: Calculating Interest During Construction in Excel
Now, I have reached the final step of my calculation for Interest During Construction in Excel. I will show you how you can calculate the Interest During Construction.
- Firstly, select the cell where you want to calculate the Interest During Construction. Here, I selected cell E26.
- Secondly, in cell E26 write the following formula.
=SUM(E17:E24)
- Finally, press Enter to get the Interest During Construction.
Read More: Calculate Interest in Excel with Payments (3 Examples)
Final Output
In the following picture, you can see the final output of my calculation for Interest During Construction in Excel.
Read More: How to Calculate Principal and Interest on a Loan in Excel
Conclusion
So, you have reached the end of my article. Here, I tried to cover the calculation of Interest During Construction in Excel. I hope it was clear to you. If you have any questions or suggestions, feel free to let me know in the comment section below.
Related Articles
- How to Create TDS Interest Calculator in Excel
- How to Create GST Interest Calculator in Excel (with Useful Steps)
- Create Post-Judgment Interest Calculator in Excel (With 2 Cases)
- How to Create FD Interest Calculator in Excel (with Easy Steps)
- How to Create Prejudgment Interest Calculator in Excel
- Perform Carried Interest Calculation in Excel (with Easy Steps)
