Finance Functions

---

The Internal Rate of Return, or IRR, is a financial tool used to measure the profitability of an investment. It represents the annualized return rate that equates the present value of future cash flows with the initial outlay.

For instance, if you’re buying a house and selling it later, IRR helps evaluate whether the return is worthwhile. You input your expected cash flows into Excel using the IRR function. It only needs a range of values showing inflows and outflows over time. The resulting rate can then be compared with other investment opportunities. Selling the house for a higher price or receiving money sooner generally pushes the IRR higher.

While IRR works well for simple cases with one outflow and one inflow, it falls short when there are multiple cash transactions during the investment period such as rental income from a property.

The standard IRR assumes all interim cash flows are reinvested at the same return rate as the original investment, which often isn’t realistic. In such situations, the MIRR function is more suitable.

MIRR requires three inputs:

1. A list of all cash flows
2. A financing rate (cost of borrowing for outflows)
3. A reinvestment rate (return on surplus funds, like rent placed in a savings account)

This approach gives a more accurate picture of profitability when cash flows vary throughout the investment’s lifespan.

The IRR and MIRR functions assume that all cash flows happen at regular intervals, usually annually. In reality, money may come in or go out at unpredictable times.

To accommodate non-uniform cash flow dates, Excel offers the XIRR function. This requires two inputs: the actual cash flows and the corresponding dates. It then computes the return based on the exact timing, producing a more precise result.

Note: There’s no single Excel function that combines the benefits of MIRR and XIRR; to factor in both irregular dates and variable reinvestment rates, manual calculations are necessary.

NPV is another critical method for evaluating investment decisions. It estimates how much value an investment adds, taking into account the time value of money.

To compute it, we discount all future expected cash flows using a required rate of return. If the total of these discounted flows exceeds the initial cost, the investment is considered viable.

Manually, each cash flow is divided by (1 + discount rate) raised to the number of years in the future. After summing the discounted values, subtract the initial investment. A positive NPV indicates that the return exceeds your minimum requirement.

Excel’s built-in NPV function simplifies this process but has a limitation: it only calculates the present value of future cash flows and doesn’t include the initial cost. To correct this, use the function for future flows and subtract the starting investment afterward.

Because this quirk can lead to errors, you may prefer to stick with manual calculations in some scenarios.

If the timing of cash flows is inconsistent, the standard NPV won’t give accurate results. In such cases, use XNPV.

This function requires three inputs: the discount rate, an array of cash flows, and their associated dates. Unlike the regular NPV, XNPV correctly factors in the time difference between cash flows and doesn’t require separating the initial investment.

To measure how a figure grows from one year to the next, use a simple formula: subtract the earlier value from the later one, then divide by the earlier value. This method is commonly used to calculate year-on-year growth in metrics like revenue or profit.

The CAGR represents the consistent annual growth rate across multiple years, smoothing out any year-to-year fluctuations.

To compute CAGR:

1. Divide the ending value by the starting value
2. Raise the result to the power of 1 divided by the number of years
3. Subtract 1 from the final result

This gives a steady annual growth rate as if the growth had occurred uniformly each year.

An amortized loan is one where the debt is repaid gradually over the loan term in equal installments. Important terms include:

• Principal: Original amount borrowed
• Interest Rate: Annual rate applied to the principal
• Term: Duration of the loan in years

To figure out the annual repayment that will fully pay off the loan by the end, use Excel’s PMT function. You’ll need the interest rate, number of periods, and the loan principal.

For monthly repayments, adjust the rate and term accordingly (divide the interest rate by 12 and multiply the periods by 12).

The PMT function only tells you the total payment. To break it down into interest and principal components for each period, use IPMT and PPMT.

These functions help you create a detailed repayment schedule:

• IPMT shows the interest portion
• PPMT gives the principal part

For each period, the remaining loan balance is calculated by subtracting the principal payment from the opening balance. Over time, interest payments decrease while principal repayments increase.

The payback period tells you how long it takes to recover your initial investment from the project’s cash inflows.

You can determine this by tracking cumulative cash flows annually and identifying the first year when the total exceeds the initial cost. A shorter payback period generally indicates a quicker recovery of your funds.

While useful as a secondary measure especially for riskier investments the payback period has some limitations.

• It ignores cash flows that occur after the break-even point
• It doesn’t account for the time value of money

Therefore, it’s best to use the payback period alongside other measures like IRR and NPV for a more complete investment evaluation.