Ratio wise R.A. and Final Bills payment amount calculations using Excel

Current Issues

Given the complexity of managing pending bills and the distribution of funds (which often arrive in multiple installments), it is essential to maintain equilibrium across various projects and contractor payments.

The most equitable approach is to allocate available funds proportionately based on the ratio of received funds to the total outstanding bills.

At the same time, priority should be given to works that are nearing completion and can be finalized with necessary adjustments.

This article explains the method of calculating proportional contractor payments using the funds received, while ensuring final bills are paid in full to close completed works — all using simple Microsoft Excel techniques.

Illustrative Example

Grand Received =					900
Expected Payment =					8118.5
Final bill payments =					440.5
R.A. Bill Payments =					7678
Grand for R.A. Bills =					459.5
Sr. No.	Work Name	Work cost	Expenditure	Demand	Bill Stage	Bill to be given
1	A	550	275	275		16.457
2	B	592	296	296		17.714
3	C	160	80	80	Final	80
7	G	748	374	374		22.382
8	H	705	352.5	352.5		21.095
22	V	181	90.5	90.5	Final	90.5
23	W	177	88.5	88.5	Final	88.5

→ Sum of "Bill to be given" = 900 (matches Grand Received)
→ All Final stage bills are paid 100% to close the works.

Formulas Used

Column Information

A → Sr. No.
B → Work Name (manual)
C → Work cost (manual)
D → Expenditure (manual)
E → Demand (manual)
F → Bill Stage (manual – "Final" or blank)
G → Bill to be given (calculated)

Main Summary Calculations

Grand Received           = Manual input

Expected Payment         = SUM(E8:E75)

Final bill payments        = SUMIF(F8:F75, "Final", E8:E75)

R.A. Bill Payments          = Expected Payment − Final bill payments

Grand for R.A. Bills       = Grand Received − Final bill payments

Most Important Formula – Bill to be given (column G)

=IF(F8="Final", E8, ($C$6 / $C$5) * E8 )

Where:
• F8 → Bill Stage of current row
• E8 → Demand (pending amount) of current row
• $C$6 → Grand for R.A. Bills (available money for RA bills)
• $C$5 → Total pending RA Bills amount (to be proportioned)

This method ensures proportional distribution of limited funds among running RA bills while guaranteeing full payment to all final bills — a very practical and fair approach.

Ratio-wise RA & Final Bills Payment

GRAND RECEIVED (Total Fund)

Total Demand (Expected) 0

Total Final Bills 0

Total RA Bills Demand 0

Available for RA Bills 0

Sr.	Work Name	Work Cost	Demand (Pending)	Bill Stage	Payment to be Made	Action
1				RA Final	0.00

Note: Final bills are paid 100%. RA bills are paid based on the remaining ratio.