How to Create Interim Payment Certificate Format in Excel


Step 1 – Create the Basic Outline


  • 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.

Create Basic Outline of interim payment certificate format in excel

Read More: How to Create Annual Loan Payment Calculator Excel

Step 2 – Mention Work Details and Bill Quantity


  • 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).

Mention Work Details and 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.

Mention Work Details and Bill Quantity to create interim payment certificate format in excel

Step 3 – Add Percent of Work Completion


  • 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.

Acknowledge Percent of Work Completion

Step 4 – Calculate the Total Due Amount


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

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.

Calculate the Total Due Amount

  • 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.

Using the Fill Handle tool

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

Calculate the Total Due Amount of interim payment certificate format in excel

  • Go to cell G17 and insert this formula:
  • Press Enter.

Calculating Total Due Amount

Read More: How to Calculate Monthly Payment in Excel

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


  • 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.

Compute the Due Amount for the Present Payment Certificate

  • Go to cell G20 and put the following formula into it:
  • Press Enter.

Present Due Amount of Interim Payment in Excel

  • Merge cells in the B22:G22 range.

  • Go to the Developer Tab.
  • Click on Visual Basic in the Code group.

Opening Visual Basic

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

Inserting Code Module

  • 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))
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)
GivenCurrency = ""
End If
GetIndex = GetIndex + 1
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
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

VBA Code

  • 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.

Calling UDF in Interim Payment Certificate Sheet in Excel

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

Currency showing in Words

Step 6 – Create the Certification and Authorization Space


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

Construct Certification and Authorization Space

Download the Practice Workbook and Template

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

Related Articles

<< Go Back to Finance TemplateExcel Templates

Get FREE Advanced Excel Exercises with Solutions!
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

  1. 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.


    • Hello Abubakar,

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


Leave a reply

Advanced Excel Exercises with Solutions PDF