Learn Investment Analysis | Dynamic Financial and Time-Based Modeling

Swipe to show menu

Loans model repayment structures, but investment analysis evaluates whether a project or opportunity actually creates value over time. In this chapter, build a dynamic investment evaluation system using discounted cash flow analysis.

XNPV Structure

=XNPV(rate, values, dates)

rate: required return or discount rate;
values: cash flow amounts;
dates: timing of each cash flow.

XNPV calculates the present value of irregular cash flows.

A positive result indicates value creation above the required return.

XIRR Structure

=XIRR(values, dates, [guess])

values: cash flow amounts;
dates: timing of each cash flow;
[guess]: optional starting estimate.

XIRR calculates the internal rate of return generated by the investment.

Step 1 Build the Cash Flow Structure

Create the following columns:

Date
Cash_Flow

Step 2 Enter the Investment Timeline

Add the following dataset:

01/05/2026,-20000
01/06/2026,4500
01/07/2026,5200
01/08/2026,6000
01/09/2026,5800
01/10/2026,4900

Negative values: capital outflows;
Positive values: future inflows.

Step 3 Define the Discount Rate

Inside E1, type:

0.10

This represents a required return of 10%.

Step 4 Calculate Net Present Value

Type:

=XNPV(E1, B2:B7, A2:A7)

E1: discount rate;
B2:B7: cash flows;
A2:A7: corresponding dates.

The formula returns the investment value in today's money.

Step 5 Interpret the Result

Positive XNPV: value creation;
Negative XNPV: value destruction.

A positive result means the investment exceeds the required return threshold.

Step 6 Calculate Internal Rate of Return

Type:

=XIRR(B2:B7, A2:A7)

The formula returns the actual annualized return generated by the investment.

Step 7 Compare Against the Required Return

Compare:

XIRR;
Discount rate in E1.

If XIRR is higher than the discount rate, the investment is financially viable.

Step 8 Test Model Sensitivity

Modify:

Cash flow values;
Cash flow timing;
Discount rate.

Confirm that:

XNPV recalculates automatically;
XIRR recalculates automatically;
Investment viability changes dynamically based on assumptions.

Everything was clear?

Thanks for your feedback!

Section 4. Chapter 4

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 4. Chapter 4

Investment Analysis

XNPV Structure

XIRR Structure

1. What is the main difference between `XNPV` and `XIRR`?

2. Why are dates required in both `XNPV` and `XIRR`?

3. What does it mean when `XIRR` is higher than the discount rate?

Investment Analysis

XNPV Structure

XIRR Structure

1. What is the main difference between XNPV and XIRR?

2. Why are dates required in both XNPV and XIRR?

3. What does it mean when XIRR is higher than the discount rate?

1. What is the main difference between `XNPV` and `XIRR`?

2. Why are dates required in both `XNPV` and `XIRR`?

3. What does it mean when `XIRR` is higher than the discount rate?