Mining Project Finance Model with Reserve Tail Ratio and DSCR Debt Sizing (Excel)

Description
Almost every mining model on the market is a discounted cash flow or a three-statement projection with a production schedule typed into a row. Both answer the investor's question - is the return above the cost of capital - and neither answers the lender's. A mine is not an annuity. It is a finite quantity of metal in the ground, and the loan has to be repaid while some of it is still there.

THE OREBODY IS THE BINDING CONSTRAINT
Ore treated in any year is the lesser of mill capacity and the reserve still standing. Cumulative tonnes are tested against the mineable reserve on the Checks tab, and when the pit is exhausted the mine stops rather than running on to the end of the model horizon. In the worked example a 132 Mt reserve through an 8 Mtpa mill gives sixteen full years and a seventeenth at four million tonnes, and then nothing.

THE TENOR IS AN OUTPUT, NOT AN ASSUMPTION
Mining lenders do not finance to the last tonne. The test they apply is the reserve tail ratio: reserves remaining at final maturity divided by reserves depleted over the loan life, against a covenant of typically a quarter to a third. This model computes that ratio for every candidate maturity, year by year, and caps the facility at the longest tenor that clears. The sponsor in the worked example asks for fourteen years; the ratio is 0.375 at year twelve and 0.269 at year thirteen, so against a 0.30 covenant the answer is twelve - and the Dashboard says so in words.

THE QUANTUM IS AN OUTPUT TOO
A lender runs two tests and lends the lesser of them: what the cash flow services at a target cover ratio, and what the asset supports as a share of cost. The DSCR capacity is computed in closed form - the present value of CFADS over the tenor divided by the target ratio, one division, no goal seek and no solver, with the year-by-year workings on the same tab - and set beside the gearing cap on capitalised project cost. Here the gearing cap binds: the cash flow would have carried 1.49 billion and the facility is 893 million.

REVENUE BUILT FROM METAL, NOT FROM AN ASSUMPTION
Contained metal is ore times head grade; recovery splits it between concentrate and tailings, and the Checks tab tests that the three tie in every single year. Concentrate tonnes follow from the concentrate grade, the smelter pays for a share of the contained metal, treatment and refining charges come off, the by-product credit goes on, and the royalty is charged on net smelter return. Mining cost is charged on total material moved - ore plus waste at the strip ratio - and processing on ore treated. Out of the same chain come C1 cash cost and all-in sustaining cost, the two numbers every mining analyst quotes.

NO CIRCULAR REFERENCES
Thirty monthly construction columns capitalise the arrangement fee, the commitment fee and the interest during construction month by month. Because interest is charged on the balance at the start of each month, the ledger resolves left to right - no cell depends on itself and iterative calculation stays switched off. The sculpting recursion is removed the same way, algebraically rather than by iteration.

TAX AND CLOSURE THE WAY A MINE ACTUALLY WORKS
Depreciation is units of production - capitalised cost written off in proportion to the tonnes treated - with the interest deduction and loss carry-forward, and a check confirms it ties to the share of the reserve mined. The closure provision is funded into a trust year by year, earns a return, and pays the rehabilitation bill in the year after the last ore is treated; a shortfall would be a cash call on the sponsor and a surplus is released.

NINETEEN INTEGRITY CHECKS
Every one must read PASS before you quote a number. Eight of them exist only because this model treats the mine as a depleting orebody: the reserve is never exceeded, the mill is never over-fed, the metal balances, the material moved ties to the strip ratio, the reserve tail clears the covenant, the trust funds the closure cost, and the depreciation matches the tonnes. A model with a typed-in production schedule cannot run them, because it never computes the quantities they test.

THE WORKED EXAMPLE
A 132 Mt reserve at 0.75 per cent copper, an 8 Mtpa mill, a 2.4 strip ratio and a seventeen-year life. Capitalised project cost of 1.62 billion, a senior facility of 893 million over twelve years, a project return of 11.2 per cent and an equity return of 15.0 per cent, a minimum cover ratio of 2.41 times, and an all-in sustaining cost of 2.35 dollars a pound against a 4.40 price deck.
Twelve tabs: Read Me, Dashboard, Assumptions, Construction & Funding, Mine Plan & Reserve, Processing & Metal, Revenue & Costs, Debt Sizing, Debt Schedule, Cash Flow, Returns, Checks. Live formulas throughout. No macros, no add-ins, no external links, no locked cells and no passwords. Two exact one-way sensitivity tables - metal price and head grade - each a full twenty-two year recalculation with the workings on the page.
This workbook models the arithmetic of your own project. It is not investment advice, and the worked example is invented.