### Step 1 – Create the Basic Outline

**Steps:**

- Give a suitable heading in the
**B2:G2**range. We’ve put**Interim Payment Certificate**. - In the
**B4:G7**range, create an area to insert the basic data and information about the project. This includes the**Project Name**,**Contractor**name,**Contract Amount**, as well as other details you might need.

**Read More:** How to Create Annual Loan Payment Calculator Excel

### Step 2 – Mention Work Details and Bill Quantity

**Steps:**

- Create a table in the
**B9:E16**range. - In the
**B9:E9**range, we put the headings as**SL**,**Description,**and**BQ Amount**(bill quantity).

- Place the first work name and short description in cell
**C10**and put its corresponding bill amount in cell**E10**.

- Insert the rest of the details.

### Step 3 – Add Percent of Work Completion

**Steps:**

- Create a new column with the heading
**% Complete**under**Column F**. - In cells in the
**F10:F16**range, write down the completion percentage of each work order.

### Step 4 – Calculate the Total Due Amount

**Steps:**

- Create a new column for
**Due Amount**. - Select cell
**G10**and enter the following formula into the**Formula Bar.**

`=E10*F10`

This formula multiplies the total bill amount in cell **E10** with the completion percentage in cell **F10** to get the due payment amount.

- Press the
**Enter**key.

- Bring the cursor to the right-bottom corner of cell
**G10**. It will look like a plus**(+)**sign. This is the**Fill Handle**tool. - Double-click on it to copy the formula through the column.

- The cells in the
**G11:G16**range get filled with the correct results.

- Go to cell
**G17**and insert this formula:

`=SUM(G10:G16)`

- Press
**Enter.**

**Read More:** How to Calculate Monthly Payment in Excel

### Step 5 – Compute the Due Amount for the Present Payment Certificate

**Steps:**

- We’ll introduce two items:
**10% retention**and**previous payments**. Retention means holding up some money for security purposes. We put it as 10%. Since it is the second interim payment certificate, the number of previous payments should be 1.

- Go to cell
**G20**and put the following formula into it:

`=G17-G18-G19`

- Press
**Enter**.

- Merge cells in the
**B22:G22**range.

- Go to the
**Develo****p****er Tab**. - Click on
**Visual Basic**in the**Code**group.

- This opens the
**Microsoft Visual Basic for Applications**window. - Move to the
**Insert**tab. - Select
**Module**from the available options.

- This opens a code module.
- Copy the following code and paste it into the code module.

```
Function ConvertCurrencytoWords(ByVal GivenCurrency)
Dim USD, C
Words = Array("", "", " Thousand ", " Million ", " Billion ", " Trillion ")
GivenCurrency = Trim(Str(GivenCurrency))
FractionCurrency = InStr(GivenCurrency, ".")
If FractionCurrency > 0 Then
C = TensPlace(Left(Mid(GivenCurrency, FractionCurrency + 1) & "00", 2))
GivenCurrency = Trim(Left(GivenCurrency, FractionCurrency - 1))
End If
GetIndex = 1
Do While GivenCurrency <> ""
GetHundred = ""
GetValue = Right(GivenCurrency, 3)
If Val(GetValue) <> 0 Then
GetValue = Right("000" & GetValue, 3)
If Mid(GetValue, 1, 1) <> "0" Then
GetHundred = OnesPlace(Mid(GetValue, 1, 1)) & " Hundred "
End If
If Mid(GetValue, 2, 1) <> "0" Then
GetHundred = GetHundred & TensPlace(Mid(GetValue, 2))
Else
GetHundred = GetHundred & OnesPlace(Mid(GetValue, 3))
End If
End If
If GetHundred <> "" Then
USD = GetHundred & Words(GetIndex) & USD
End If
If Len(GivenCurrency) > 3 Then
GivenCurrency = Left(GivenCurrency, Len(GivenCurrency) - 3)
Else
GivenCurrency = ""
End If
GetIndex = GetIndex + 1
Loop
Select Case USD
Case ""
USD = "No USD"
Case "One"
USD = "One Dollar"
Case Else
USD = USD & " USD"
End Select
Select Case C
Case ""
C = " and No C"
Case "One"
C = " and One Cent"
Case Else
C = " and " & C & " C"
End Select
ConvertCurrencytoWords = USD & C
End Function
Function TensPlace(TensDigit)
Dim Output As String
Output = ""
If Val(Left(TensDigit, 1)) = 1 Then
Select Case Val(TensDigit)
Case 10: Output = "Ten"
Case 11: Output = "Eleven"
Case 12: Output = "Twelve"
Case 13: Output = "Thirteen"
Case 14: Output = "Fourteen"
Case 15: Output = "Fifteen"
Case 16: Output = "Sixteen"
Case 17: Output = "Seventeen"
Case 18: Output = "Eighteen"
Case 19: Output = "Nineteen"
Case Else
End Select
Else
Select Case Val(Left(TensDigit, 1))
Case 2: Output = "Twenty "
Case 3: Output = "Thirty "
Case 4: Output = "Forty "
Case 5: Output = "Fifty "
Case 6: Output = "Sixty "
Case 7: Output = "Seventy "
Case 8: Output = "Eighty "
Case 9: Output = "Ninety "
Case Else
End Select
Output = Output & OnesPlace(Right(TensDigit, 1))
End If
TensPlace = Output
End Function
Function OnesPlace(OnesDigit)
Select Case Val(OnesDigit)
Case 1: OnesPlace = "One"
Case 2: OnesPlace = "Two"
Case 3: OnesPlace = "Three"
Case 4: OnesPlace = "Four"
Case 5: OnesPlace = "Five"
Case 6: OnesPlace = "Six"
Case 7: OnesPlace = "Seven"
Case 8: OnesPlace = "Eight"
Case 9: OnesPlace = "Nine"
Case Else: OnesPlace = ""
End Select
End Function
```

- Save the file in the macro-enabled worksheet (.xlsm) format and return to the worksheet.
- In cell
**C22**, start to write**=con**and you’ll see the function**ConvertCurrencytoWords**in the suggestion. Double-click on it.

- The result and formula in cell
**C22**look like the following.

### Step 6 – Create the Certification and Authorization Space

**Steps:**

- We built an isolated area in cells in the
**B24:G36**range. In this section, they can give their opinions and signatures.

**Download the Practice Workbook and Template**

You may download the following Excel workbook to use as a template.

**Related Articles**

- How to Create Snowball Payment Calculator in Excel
- Create Progressive Payment Calculator in Excel
- How to Create Line of Credit Payment Calculator in Excel
- Make a Cash Payment Voucher Format in Excel
- How to Calculate Car Payment in Excel
- How to Calculate Auto Loan Payment in Excel

**<< Go Back to Finance Template | Excel Templates**

A brilliant article. I really learn a lot from this article and it will help. I hope I will be able to create an interim certificate template following these steps.

Thanks

Hello

Abubakar,You are most welcome. We hope so you will be able to create an interim certificate template following these steps.

Regards

ExcelDemy