A modified investment return needs a visible reinvestment assumption

A modified internal rate of return, usually shortened to MIRR, is partly a statement about what happens to money before the project ends. Two analysts can use the same operating cash flows and report different modified returns because they assume different earnings on earlier receipts. Ask for that assumption before treating a change in the percentage as an improvement in the project itself.

Microsoft's MIRR function takes a sequence of periodic cash flows, a finance rate and a reinvestment rate. Payments are negative and receipts are positive. The two rate arguments serve different calculations; they are not interchangeable labels for the same input. Keep their units and the cash-flow interval explicit in the model.

Record who selected each rate, what evidence supports it and whether it is an illustrative scenario or an approved planning assumption. A percentage copied from an older workbook can remain plausible while describing a funding or cash-management arrangement the company no longer uses. The calculation needs an owner who can explain the inputs.

Consider a hypothetical project that pays $100,000 today, receives $40,000 at the end of year one, receives nothing at the end of year two and receives $85,000 at the end of year three. Assume the earlier receipt earns 5% annually until the project ends. The example excludes tax, fees and other cash movements to isolate the return calculation.

The first receipt has two years to accumulate. Its modeled terminal amount is $40,000 multiplied by 1.05 squared, or $44,100. The last receipt arrives at the terminal date, so it remains $85,000. Together they produce a terminal amount of $129,100. No operating forecast changed when the first receipt was compounded.

The present outlay is $100,000 because the only payment occurs today. The annual modified return is therefore ($129,100 / $100,000) raised to the power of one-third, less one: approximately 8.89%. There are four dated entries but three annual intervals. Counting entries as elapsed years would give a different and incorrect answer for this schedule.

Keep the accumulated receipt as a modeling bridge rather than a forecast bank balance. The company might use the cash to fund other activity instead of leaving it in an account. The result describes the stated reinvestment assumption; it does not establish that $129,100 will actually be sitting in cash at the third year-end.

Now change only the assumed reinvestment rate from 5% to 10%. The year-one receipt becomes $48,400 at the terminal date, giving total accumulated receipts of $133,400. The resulting MIRR is approximately 10.08%. The original customer receipts still total $125,000; the higher reported return comes from the additional accumulation assumption.

ACCA's discussion demonstrates how changing the reinvestment input changes MIRR. That makes a small sensitivity table useful when the assumed use of interim cash is uncertain. Label the table by its actual input so a reader does not mistake a reinvestment scenario for stronger sales, lower project spending or a later disposal gain.

Ask whether the proposed rate reflects an available opportunity, a broad planning convention or an aspiration. If the business expects to distribute the interim receipt or spend it on obligations outside this analysis, explain that intended use. A model can still present a conventional modified return, but it should not conceal the difference between the convention and the operating plan.

The finance rate discounts negative cash flows to the beginning of the modeled period. In the simple example, changing that input alone does not alter the answer: the only negative amount is already at time zero. This is a useful diagnostic when a reviewer expects every rate cell to move every output.

A project with a later construction payment or closure outlay has a different pattern. Show each negative payment on its date, calculate its present amount using the stated finance rate and combine those amounts for the denominator. Do not move a later payment to the first period merely to make the worksheet resemble a simpler example.

Keep this modeling input separate from a lender's quoted annual percentage rate and from the project's entire funding assessment. Fees, payment timing and borrowing terms may require a more detailed schedule. The name of the MIRR argument does not establish which financial assumption is appropriate for the company's decision.

The zero in year two is meaningful: a year passes without a modeled receipt or payment. Deleting that period changes the example to two intervals, with the final $85,000 arriving a year earlier. At the same 5% reinvestment assumption, that shorter sequence gives approximately 12.69%. It is a different investment schedule, not a harmless presentation cleanup.

Microsoft documents that numeric zeros are included while empty cells and certain nonnumeric values are ignored. Represent a known no-cash period with a numeric zero, and investigate missing data before calculating. An empty cell should not silently mean both an intentional pause and an unprepared forecast.

Match rates to the interval. Monthly cash flows require a supported monthly basis, and the resulting periodic return needs a clearly explained annual conversion if an annual figure is presented. For irregular payment dates, use a dated analysis suitable for the decision rather than pretending that uneven intervals are equal.

Retain the signed schedule, number of intervals, present outlay, accumulated receipts and both rate inputs beside the result. Another reviewer should be able to reproduce the percentage without guessing how the periods were counted. Include the net present value and actual funding schedule when management is deciding whether the commitment is worthwhile.

If alternatives have different sizes or service lives, resolve those differences before ranking them by a modified percentage. A larger percentage does not by itself establish a larger contribution to business value. Also show any later cash obligation that management must be able to fund even if the displayed return looks attractive.

When updating the forecast, bridge the movement between changed project cash flows and changed rate assumptions. Preserve the prior accepted version. That lets management see whether the investment improved, the proposed use of interim cash changed, or a timing error was corrected.