Calculation of Interest During Construction in Excel

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

We will use the following sample dataset for illustration. 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.

Calculation of Interest During Construction in Excel

Step 1 – Select Drawdown Mode and Frequency

Create a section for entering the Drawdown Mode and Drawdown Frequency.

Select Drawdown Mode and Frequency for Calculation of Interest During Construction in Excel

Select the cell where you want to show the Drawdown Mode. We have selected cell C12.
Go to the Data tab.
Select Data Validation.

Using Data Validation for Calculation of Interest During Construction in Excel

The Data Validation dialog box will appear.
Select the Settings tab.
Click on the drop-down button for Allow from Validation criteria.
Select List.

Data Validation Dialog Box for Claculation of Interest During Construction in Excel

Enter the options you want in the Source section.
Select OK.

You will see a drop-down button has been inserted in your selected cell which is cell C12.

Drop-down button for Calculation of Interest During Construction in Excel

To select Drawdown Mode, first Click on the drop-down button.
Select the mode you want. we have selected Quarterly.

You will see that Quarterly is showing as Drawdown Mode.

Select the cell where you want to show the Drawdown Frequency. we have selected cell C13.
In cell C13, enter the following formula.

=IF(C12="Annually",12,IF(C12="Half Yearly",6,3))

Using IF function For Drawdown Frequency for Calculation of Interest During Construction in Excel

Press Enter and you will get your Drawdown Frequency.

How Does the Formula Work?

IF(C12=”Half Yearly”,6,3):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)): the IF function returns 12 if the logical_test is True. Otherwise, it will go to the next IF function.

Step 2 – Create Table for Interest During Construction

Create a table with 4 columns.
Name the columns as Date, Drawdown Amount, Cumulative Amount and Interest.

Create Table for Calculation of Interest During Construction in Excel

Step 3 – Insert Dates into Table

We will insert the Dates for every drawdown period for the project.

Select the cell where you want the first Date.
Enter the following formula in that selected cell.

=C4

Insert Dates into Table

Press Enter to get the Date.

Inserting Dates for Calculation of Interest During Construction in Excel

The formula returns the value that is in cell C4. In this case, it returns the Start Date of the construction period.

Select the cell where you want the second Date. we have selected cell B18.
In cell B18 enter the following formula.

=IF(B17<$C$5,EDATE(B17,$C$13),"")

Press Enter to get the Date.

Using IF and EDATE functions for Calculation of Interest During Construction in Excel

Drag the Fill Handle down to copy the formula to the other cells.

Dragging Fill Handle for Calculation of Interest During Construction in Excel

How Does the Formula Work?

EDATE(B17,$C$13): In the EDATE function, we 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),””): 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 Otherwise, it returns blank.

Note: We have used Absolute Cell References for the fixed values so that the formula does not change while using Autofill.

You can see that we have copied the formula to the other cells and got the desired output.

In the following image, you can see that the last date is after the End Date. So, we will delete this date.

This is how the table looks like at this stage.

Step 4 – Calculate Drawdown Amount

Select the cell where you want to calculate the Drawdown Amount for Year 1.
Enter the following code in that selected cell.

=$C$9/(12/$C$13)

Calculate Drawdown Amount for Calculation of Interest During Construction in Excel

Press Enter to get the result.

Drag the Fill Handle down to copy the formula to the remaining cells.

In the formula, 12 is divided by the value in the cell C13 which is the Drawdown Frequency. The Debt in Year 1 is divided by the result.

You can see that we have copied the formula and got the Drawdown Amount for Year 1.

Select the cell where you want to calculate the Drawdown Amount for Year 2. we have selected cell C21.
In cell C21, enter the following formula.

=$C$10/(12/$C$13)