Finance Applications and Investment Analysis

Introduction

Finance transforms accounting information into decisions about borrowing, liquidity, investment, risk and value. Spreadsheet models are particularly useful because they connect assumptions, cash-flow forecasts, financial functions and decision criteria in an auditable structure.

This chapter develops Excel-based applications for the time value of money, loan amortisation, working capital, capital budgeting, risk and return, and valuation. The emphasis is not merely on obtaining an answer, but on constructing models whose assumptions, units, timing and formulas can be reviewed independently.

Learning Objectives

After completing this chapter, students should be able to:

1. Apply present-value and future-value principles to single amounts and annuities.
2. Use Excel financial functions with correct timing and sign conventions.
3. construct and interpret a loan amortisation schedule.
4. Analyse working capital and the cash conversion cycle.
5. Evaluate investments using NPV, IRR, profitability index and payback.
6. Model expected return, risk, diversification and required return.
7. Apply discounted cash-flow valuation under defensible assumptions.
8. perform sensitivity and scenario analysis.
9. Identify modelling, governance and ethical risks in spreadsheet-based finance.

1. Time Value of Money

A monetary amount received today is normally more valuable than the same nominal amount received later. Current funds can earn a return, while future funds are exposed to inflation, uncertainty and opportunity cost. Time-value-of-money calculations require consistency among the interest rate, cash-flow frequency and number of periods.

1.1 Future value of a single amount

For compound interest:

where FV is future value in currency units, PV is present value, r is the effective interest rate per compounding period, and n is the number of periods. The formula assumes reinvestment at a constant periodic rate.

Example. An investment of $20,000 earns 7% annually for four years:

In Excel, either =20000*(1+7%)^4 or =FV(7%,4,0,-20000) returns $26,215.92. The negative present value in FV represents a cash outflow from the investor.

1.2 Present value of a single amount

Rearranging the future-value equation gives:

This discounts a future amount to its value at time zero. For example, $10,000 receivable in one year has a present value of $9,259.26 at an 8% discount rate.

1.3 Nominal and effective rates

If a nominal annual rate j is compounded m times per year, its effective annual rate is:

For a nominal annual rate of 12% compounded monthly, the monthly rate is 1% and:

A monthly model must use 1% per month, not 12% per month. Conversely, dividing an effective annual rate by 12 does not generally produce an equivalent monthly rate. The equivalent monthly rate is (1+EAR)^1/12-1.

1.4 Ordinary annuities and annuities due

An ordinary annuity consists of equal payments at the end of each period. Its present value is:

Here, PMT is the periodic payment, r is the periodic rate and n is the number of payments. The expression follows by summing the geometric sequence PMT/(1+r)+PMT/(1+r)²+\cdots+PMT/(1+r)ⁿ. It applies when payments and rates are constant and payment intervals match compounding intervals.

An annuity due pays at the beginning of each period:

The additional factor arises because every payment is discounted for one fewer period. In Excel, the optional type argument is 0 for end-of-period payments and 1 for beginning-of-period payments.

1.5 Core Excel financial functions

Excel follows a cash-flow sign convention: amounts paid and amounts received should have opposite signs. If a loan receipt is entered as positive, repayments will normally be returned as negative.

2. Loan Amortisation

An amortising loan is repaid through periodic instalments containing both interest and principal. For a fully amortising, fixed-rate loan with end-of-period payments and no residual balance, the payment follows from the ordinary-annuity equation:

where P is the initial principal in currency units, r is the periodic interest rate and n is the number of payments.

Worked example: five-year loan

A business borrows $100,000 at a nominal annual rate of 6%, compounded monthly, for 60 months. The monthly rate is 0.06/12=0.005. Therefore:

An Excel formula is =-PMT(6%/12,60,100000).

For each month t:

Minor differences in the final payment can arise if displayed payments are rounded to cents while calculations use unrounded values. A robust schedule should calculate with full precision and adjust the final payment if required.

Figure: Each payment has the same total height; as the outstanding balance declines, interest decreases and principal repayment increases.

A loan schedule should reconcile the opening principal with total principal repaid and should end at approximately zero. Interest expense must also be distinguished from the repayment of the liability in accounting records and cash-flow classification.

3. Working Capital and Liquidity

Net working capital measures short-term financial resources:

Positive net working capital may support liquidity, but an excessively high balance can indicate slow inventory, weak collection or inefficient use of cash. The operating components require more detailed analysis.

3.1 Cash conversion cycle

The cash conversion cycle estimates the time between paying suppliers and collecting cash from customers:

where DIO is days inventory outstanding, DSO is days sales outstanding and DPO is days payable outstanding.

Common approximations are:

If purchases are unavailable, cost of goods sold is sometimes used as a proxy for DPO, but that approximation should be disclosed. Average balance-sheet amounts are preferable to year-end balances because seasonal values may be misleading.

Example. A company has average inventory of $500,000, receivables of $360,000 and payables of $400,000. Annual COGS is $3.65 million, annual credit sales are $4.38 million, and annual credit purchases are $3.65 million. Daily COGS and purchases are $10,000, while daily sales are $12,000. Thus, DIO is 50 days, DSO is 30 days and DPO is 40 days. The cash conversion cycle is 40 days.

Reducing DIO by five days releases approximately $50,000 of inventory investment. Reducing DSO by three days releases approximately $36,000 of receivables. These estimates assume activity levels remain constant and operational service is not damaged.

Working-capital improvement must not be pursued mechanically. Delaying legitimate supplier payments can damage relationships, while aggressive collection or inventory reduction can harm customers and production continuity.

4. Capital Budgeting

Capital budgeting evaluates long-term investments through incremental after-tax cash flows. Relevant amounts include initial investment, changes in working capital, operating cash flows, taxes, disposal proceeds and opportunity costs. Sunk costs are excluded because they cannot be changed by the decision.

4.1 Net present value

For cash flows occurring at equal intervals:

where CF_t is net cash flow at time t, k is the risk-appropriate periodic discount rate and n is the final period. Cash flows and the discount rate must both be nominal or both real, use the same currency and reflect compatible risk assumptions.

Decision rule: accept an independent project if NPV is positive, provided the forecasts and discount rate are reliable. A positive NPV indicates expected value creation in present-value terms.

Excel's NPV function discounts its first listed cash flow by one period. Therefore, an immediate investment in cell B5 and end-of-year cash flows in C5:F5 should be entered as =B5+NPV(rate,C5:F5), where B5 is negative. XNPV should be used for irregularly dated cash flows.

4.2 Internal rate of return

IRR is a discount rate satisfying:

For conventional cash flows—one initial outflow followed by inflows—the project is generally acceptable when IRR exceeds the required return. However, non-conventional cash flows can produce multiple IRRs or no economically meaningful IRR. NPV is normally preferred for mutually exclusive projects because it measures absolute value creation and assumes reinvestment at the cost of capital rather than at the IRR.

Excel uses IRR(values,[guess]) for equally spaced cash flows and XIRR(values,dates,[guess]) for actual dates. Both are iterative functions.

4.3 Other appraisal measures

The profitability index is:

A PI above 1 corresponds to a positive NPV for a conventional project. It can help under capital rationing, although project indivisibility and interdependence may require optimisation rather than simple ranking.

Payback measures how long undiscounted cash inflows take to recover the initial investment. Discounted payback uses discounted inflows. Both are useful indicators of liquidity exposure, but neither captures all value created after the cutoff date.

5. Risk, Return and Portfolio Relationships

5.1 Expected return and standard deviation

For discrete scenarios:

where p_s is the probability of scenario s, R_s is its return, and probabilities sum to 1.

Risk is often represented by variance and standard deviation:

Suppose an investment returns −10%, 12% and 30% with probabilities 0.25, 0.50 and 0.25. Its expected return is 11%. Using returns in decimal form, variance is 0.0201 and standard deviation is approximately 14.18%. Standard deviation captures dispersion, not every dimension of risk; it treats upside and downside deviations symmetrically.

5.2 Diversification

For a two-asset portfolio:

Here, w_A and w_B are portfolio weights summing to 1, \sigma_A and \sigma_B are return standard deviations, and \rho_AB is correlation between −1 and +1. Diversification reduces risk when returns are not perfectly positively correlated. Excel functions such as AVERAGE, STDEV.S, COVARIANCE.S and CORREL support analysis of historical samples, but historical relationships may not persist.

5.3 Capital Asset Pricing Model

The CAPM estimates a required return for systematic risk:

where R_f is the risk-free rate, \beta_i measures sensitivity to market returns and E(R_m)-R_f is the market risk premium. If the risk-free rate is 3%, beta is 1.2 and expected market return is 9%, the required return is 10.2%.

CAPM assumes competitive markets and that systematic risk is the priced risk. In practice, beta estimation, market selection and changing risk can materially affect results.

6. Valuation Models and Assumptions

6.1 Bond valuation

A plain fixed-rate bond is valued as the present value of coupons and principal:

where C is the periodic coupon, F is face value, y is yield per coupon period and n is the number of remaining coupons. The model assumes promised payments occur; credit risk must be reflected in the yield or through expected cash flows.

6.2 Constant-growth dividend model

For a stable dividend-paying company:

where D₁ is next period's dividend, k_e is the required equity return and g is the perpetual dividend growth rate. The condition k_e>g is essential. This special-case model is unsuitable for firms without stable dividends or defensible perpetual growth.

6.3 Enterprise discounted cash flow

Enterprise value can be estimated by discounting free cash flow to the firm at the weighted average cost of capital. A common terminal-value model is:

where FCF_n+1 is free cash flow in the first year after the explicit forecast, WACC is the discount rate and g is perpetual growth. The condition WACC>g must hold. Long-run growth should normally be consistent with sustainable economic growth; otherwise, the model can imply an implausibly large firm.

Terminal value is discounted back to the valuation date. Enterprise value is then adjusted for net debt and other non-operating claims or assets to derive equity value. Because terminal value can dominate the result, sensitivity testing of WACC, growth and margins is essential.

7. Illustrative Case: Meridian Packaging Investment

Background and problem

Meridian Packaging is an illustrative company considering an automated production line. Equipment and installation require $500,000 immediately, and initial working capital requires another $50,000. Management forecasts after-tax net cash inflows of $160,000, $180,000, $200,000 and $190,000 over four years. At the end of year 4, the equipment is expected to yield $40,000 after tax and the $50,000 working-capital investment will be recovered.

The required return is 10%. The task is to determine whether the project creates value.

Approach and analysis

The cash-flow row is:

Year 4 includes $190,000 operating cash flow, $40,000 disposal proceeds and $50,000 working-capital recovery.

The present value of future inflows is approximately $635,722, producing:

The profitability index is approximately 635{,}722/550{,}000=1.156. Excel's IRR function gives approximately 16.4%.

Outcome

On the stated assumptions, Meridian should accept the project because NPV is positive and IRR exceeds the 10% required return. However, the conclusion changes at sufficiently high discount rates, and actual value depends on the reliability of demand, cost, disposal and working-capital assumptions.

Learning points

1. Working capital is an initial cash outflow even though it is not an expense.
2. Recoverable working capital and disposal proceeds belong in terminal cash flow.
3. NPV and IRR can support the same decision for a conventional independent project.
4. Sensitivity analysis reveals which assumptions could reverse the decision.
5. Forecast cash flows should be after tax and incremental to the organisation.

8. Designing a Reliable Excel Model

A well-designed workbook typically separates four layers:

1. Inputs: rates, dates, forecast drivers and documented sources.
2. Calculations: formulas without unnecessary hard-coded constants.
3. Outputs: NPV, IRR, ratios, schedules and decision indicators.
4. Controls: reconciliation tests, error flags and reasonableness checks.

Useful controls include testing that loan principal repayments equal the amount borrowed, scenario probabilities sum to 100%, and sources of funds equal uses of funds. Excel Data Tables can evaluate one- or two-variable sensitivity, while Scenario Manager can compare internally consistent cases. Goal Seek can identify a break-even price, volume or discount rate.

Avoid embedding assumptions inside long formulas. Use descriptive labels, named ranges where appropriate, consistent units and a visible model date. Lock formula cells in operational workbooks and maintain version control.

9. Limitations, Governance and Contemporary Practice

Spreadsheet models are vulnerable to formula deletion, incorrect references, copied errors, inconsistent timing and optimistic assumptions. Precision in displayed output does not imply accuracy in the underlying forecast. Independent review should address both mechanical correctness and economic reasonableness.

Ethical modelling requires neutral assumptions, transparent disclosure of uncertainty and avoidance of manipulated discount rates or terminal values designed to justify a preferred outcome. Regulated financial decisions may also require compliance with accounting standards, consumer-credit rules, market-conduct requirements, tax law and data-protection obligations.

Modern finance teams increasingly connect spreadsheets to enterprise systems, databases and business-intelligence tools. AI can assist with formula explanation, anomaly detection and scenario generation, but AI-produced formulas and forecasts must be independently validated. Confidential financial data should not be submitted to unapproved systems. Human accountability remains essential for assumptions, controls and final decisions.

Best practice is to maintain an assumption register, use actual dates when timing is irregular, reconcile model outputs to accounting records, stress-test material drivers, document overrides and obtain independent approval for high-value decisions.

Chapter Summary

Time-value-of-money models convert cash flows occurring at different dates into comparable values. Excel functions such as PV, FV, PMT, NPV, XNPV, IRR and XIRR make these calculations efficient, but require consistent timing, rates and cash-flow signs. Loan amortisation divides equal payments into declining interest and increasing principal components.

Working-capital analysis links inventory, receivables and payables to liquidity through the cash conversion cycle. Capital budgeting uses incremental after-tax cash flows, with NPV generally providing the strongest value-creation criterion. Risk analysis uses expected return, standard deviation, correlation and beta, while valuation depends critically on discount rates, growth and terminal assumptions. Reliable spreadsheet practice requires structured design, reconciliation, sensitivity analysis, documentation, ethical forecasting and independent review.

Key Terms

Amortisation: Systematic repayment of a loan through principal and interest payments.

Annuity: A series of equal cash flows occurring at regular intervals.

Cash conversion cycle: Days between cash payment for inputs and collection from customers.

Correlation: Standardised measure of the co-movement between two variables.

Discount rate: Required periodic return used to convert future cash flows into present values.

Diversification: Reduction of portfolio risk by combining imperfectly correlated investments.

Effective annual rate: Actual annual return or cost after within-year compounding.

Expected return: Probability-weighted average of possible returns.

Free cash flow: Cash flow available to capital providers after operating and investment requirements.

Internal rate of return: Discount rate at which a project's NPV equals zero.

Net present value: Present value of project inflows less the present value of outflows.

Net working capital: Current assets minus current liabilities.

Opportunity cost: Benefit sacrificed by choosing one alternative over another.

Payback period: Time required for project cash inflows to recover the initial investment.

Profitability index: Present value of future inflows divided by initial investment.

Sensitivity analysis: Examination of how changing one assumption affects an output.

Standard deviation: Square root of variance and a measure of return dispersion.

Systematic risk: Market-related risk that cannot generally be eliminated through diversification.

Terminal value: Estimated value of cash flows beyond an explicit forecast period.

Weighted average cost of capital: Required return reflecting the weighted costs of debt and equity financing.

Review Questions

Short Answer Questions

1. Why must the periodic interest rate match the frequency of cash flows?
2. Explain Excel's sign convention for financial functions.
3. What causes the interest component of an amortising loan payment to decline?
4. Define the cash conversion cycle and its three components.
5. Why is working-capital recovery included in a project's terminal cash flow?

Long Answer Questions

1. Explain and compare present value, future value, ordinary annuities and annuities due.
2. Describe how to construct and audit a complete loan amortisation schedule in Excel.
3. Critically compare NPV, IRR, profitability index and payback as investment criteria.
4. Explain how expected return, standard deviation, correlation and beta contribute to investment analysis.
5. Discuss the assumptions and limitations of discounted cash-flow and constant-growth valuation models.

Analytical / Application Questions

1. A company borrows $240,000 for seven years at 8.4% nominal interest compounded monthly. Construct the first three rows of an amortisation schedule and explain the accounting treatment of each payment.
2. Using suitable assumptions, build an Excel model showing how changes in inventory, receivable and payable days affect cash requirements and liquidity risk.
3. Evaluate a four-year project under base, optimistic and pessimistic scenarios. Calculate NPV and IRR, identify break-even assumptions and recommend appropriate governance controls.

Multiple Choice Questions with Answers

Question 1

A payment of $10,000 will be received in one year. What is its present value at an effective annual discount rate of 8%?

A. $8,000.00

B. $9,259.26

C. $10,800.00

D. $10,925.93

Correct Answer: B. $9,259.26

Question 2

A $100,000 loan bears interest at 6% nominal per year compounded monthly. What is the interest component of the first monthly payment?

A. $500.00

B. $600.00

C. $1,433.28

D. $1,933.28

Correct Answer: A. $500.00

Question 3

Which Excel formula correctly calculates project NPV when B5 contains the immediate negative investment and C5:F5 contain end-of-year cash flows?

A. =NPV(rate,B5:F5)

B. =B5+NPV(rate,C5:F5)

C. =FV(rate,4,B5,C5:F5)

D. =IRR(C5:F5)+B5

Correct Answer: B. =B5+NPV(rate,C5:F5)

Question 4

A company has current assets of $720,000 and current liabilities of $480,000. What is its net working capital?

A. $240,000

B. $480,000

C. $720,000

D. $1,200,000

Correct Answer: A. $240,000

Question 5

A business has DIO of 50 days, DSO of 30 days and DPO of 40 days. What is its cash conversion cycle?

A. 20 days

B. 40 days

C. 80 days

D. 120 days

Correct Answer: B. 40 days

Question 6

In the Meridian Packaging case, what is the approximate NPV at a 10% required return?

A. −$40,895

B. $16,830

C. $56,693

D. $85,722

Correct Answer: D. $85,722

Question 7

An investment has returns of −10%, 12% and 30% with probabilities of 0.25, 0.50 and 0.25. What is its expected return?

A. 8%

B. 10%

C. 11%

D. 14%

Correct Answer: C. 11%

Question 8

Under CAPM, what is the required return if the risk-free rate is 3%, beta is 1.2 and the expected market return is 9%?

A. 7.2%

B. 9.0%

C. 10.2%

D. 13.8%

Correct Answer: C. 10.2%

Question 9

A share is expected to pay a dividend of $2 next year. If the required equity return is 10% and perpetual growth is 4%, what is its value under the constant-growth dividend model?

A. $20.00

B. $25.00

C. $33.33

D. $50.00

Correct Answer: C. $33.33

Question 10

Why can IRR be unreliable for a project with non-conventional cash flows?

A. It always ignores the initial investment

B. It cannot be calculated using spreadsheet software

C. It may produce multiple rates that make NPV equal to zero

D. It discounts every cash flow at the risk-free rate

Correct Answer: C. It may produce multiple rates that make NPV equal to zero