1.3 Measuring Returns: CAGR, XIRR, NPV and Rolling Returns
Did your investment do well? Absolute return, CAGR, the gap between average return and CAGR, log returns, NPV and IRR for decisions, XIRR for a SIP with redemptions (solved by Newton's method), and rolling returns that expose the point-to-point trap.
📍 The situation
Meera, 34, has run a ₹10,000 monthly SIP in a hypothetical "Fund A — Flexi Cap, Direct Growth" for two years. Midway she redeemed ₹50,000 for a medical bill. Her statement says: invested ₹2,40,000, redeemed ₹50,000, current value ₹2,29,187. Her colleague divides the ₹39,187 gain by ₹2,40,000 and calls it "16% in two years, so 8% a year — no better than an FD". Her brother says his own fund "averaged 10% a year" over five years, although ₹1 lakh in it has grown only to ₹1.41 lakh. Her father is wondering whether ₹2.4 lakh of rooftop solar panels "pays", and a factsheet for another fund advertises a three-year return of 22.6% a year. Which of these numbers mean what they seem to?
All of them are questions about measuring a return. A single number has to summarise money that went in and came out at different dates, in years that were good and bad. In this section we build the honest measures: CAGR for a lump sum, the geometric (not arithmetic) mean for a sequence of yearly returns, log returns that add up over time, NPV and IRR for decisions, XIRR for real SIPs (found with Newton's method from calculus), and rolling returns that show a fund's whole record rather than one flattering start date.
NAVs, prices, fund returns and interest rates on this page are hypothetical or simulated, chosen to make the arithmetic realistic. They are not the record of any real fund or index, and they are not forecasts. Past returns, however measured, do not predict future returns. Rules on how funds must report performance, and the tax on gains, are set by SEBI, AMFI and the Income Tax Department and can change; check the current position. People, funds and products are hypothetical, and nothing here is investment advice.
Absolute Return, CAGR and Annualising
Start with the simplest case: one amount invested on one date and valued on another. A mutual-fund unit bought at an NAV of ₹45.20 and worth ₹78.60 later has clearly gained — but "73.9%" means very different things over two years and over ten. To compare investments held for different lengths of time, we convert every return to a rate per year, using the compound growth of §1.1.
Suppose a value $V_0\gt0$ becomes $V_1\ge0$ after $t\gt0$ years (for dates, $t=\text{days}/365$), with no money added or withdrawn in between.
- The absolute (holding-period) return is $R=\dfrac{V_1}{V_0}-1$.
- The compound annual growth rate (CAGR) is the constant yearly rate $g$ that turns $V_0$ into $V_1$ in $t$ years with annual compounding: $V_0(1+g)^t=V_1$.
- Annualising a return $R$ earned over $t$ years means converting it to the equivalent CAGR; de-annualising a yearly rate $g$ means finding the total return over $t$ years.
Under the conditions of Definition 1.10, $$g=\left(\frac{V_1}{V_0}\right)^{1/t}-1=(1+R)^{1/t}-1,$$ and a yearly rate $g$ held for $t$ years gives the total return $$R=(1+g)^t-1.$$ For a fund, $V_1/V_0$ is simply the ratio of the NAVs on the two dates (in a growth option, where no payouts are made).
Why. Solve $V_0(1+g)^t=V_1$: divide by $V_0$ and take the $t$-th root. The two formulas are inverse functions of each other. Note that $g$ is not $R/t$: dividing by $t$ ignores compounding, exactly the simple-interest mistake of §1.1. ∎
Average Return vs CAGR, and Log Returns
A fund's yearly returns over five years were +40%, −25%, +30%, −10% and +15%. Their average is exactly 10%, so a salesperson might say "10% a year". But ₹1,00,000 at 10% a year for five years would become ₹1,61,051, while the fund actually turned it into ₹1,41,278. The average is the wrong kind of mean for growth.
For period returns $R_1,\dots,R_n$, each greater than $-1$:
- the arithmetic mean return is $A=\dfrac{R_1+\cdots+R_n}{n}$;
- the geometric mean return is $$G=\big[(1+R_1)(1+R_2)\cdots(1+R_n)\big]^{1/n}-1.$$
Since the value is multiplied by $(1+R_1)\cdots(1+R_n)$ over the $n$ periods, $G$ is exactly the CAGR over those periods (Theorem 1.12). $A$ is the right number to forecast a single typical year; $G$ is the right number to describe growth over many years.
For returns $R_1,\dots,R_n\gt-1$, $$G\le A,$$ with equality if and only if all the $R_k$ are equal. For returns of moderate size, with $\sigma$ the standard deviation of the $R_k$ (population formula), $$G\approx A-\frac{\sigma^2}{2}.$$ The gap $\sigma^2/2$ is called volatility drag: more variable returns compound to less.
Proof. Put $x_k=1+R_k\gt0$ and let $M=\frac1n\sum x_k=1+A$. We use a fact from calculus: $\log y\le y-1$ for all $y\gt0$, with equality only at $y=1$ (the line $y-1$ is the tangent to the concave curve $\log y$ at $y=1$). Apply it to $y=x_k/M$ and add: $$\sum_{k=1}^n\log\frac{x_k}{M}\le\sum_{k=1}^n\Big(\frac{x_k}{M}-1\Big)=\frac{nM}{M}-n=0.$$ So $\sum\log x_k\le n\log M$, i.e. $(x_1\cdots x_n)^{1/n}\le M$, which is $1+G\le1+A$. Equality needs every $x_k/M=1$, i.e. all returns equal. (This is the AM–GM inequality.)
The approximation. For small $R$, $\log(1+R)\approx R-\frac{R^2}{2}$. Averaging over the $n$ periods, $\log(1+G)=\frac1n\sum\log(1+R_k)\approx A-\frac{A^2+\sigma^2}{2}$, because the mean of $R_k^2$ is $A^2+\sigma^2$. The left side is also $\approx G-\frac{G^2}{2}$, and with $G\approx A$ the squared terms cancel, leaving $G\approx A-\frac{\sigma^2}2$. ∎
Log returns. Simple returns multiply over time: $(1+R_1)(1+R_2)\cdots$. Taking logarithms turns that product into a sum, which is why analysts and finance theory often work with log returns (the continuous compounding of §1.1 in reverse).
If a price moves from $P_0\gt0$ to $P_1\gt0$, the log return is $r=\log\dfrac{P_1}{P_0}=\log(1+R)$, where $\log$ is the natural logarithm (LN in a spreadsheet) and $R$ is the simple return. Equivalently $P_1=P_0e^{r}$: $r$ is the continuously compounded rate over the period.
- For prices $P_0,P_1,\dots,P_n\gt0$ with period log returns $r_k=\log(P_k/P_{k-1})$, $$\log\frac{P_n}{P_0}=r_1+r_2+\cdots+r_n.$$
- Conversions: $R=e^r-1$ and $r=\log(1+R)\le R$, with equality only when $R=0$.
- $\log(1+\text{CAGR})$ is the average yearly log return; a mean daily log return $\bar r$ corresponds to an annual log return of about $252\,\bar r$ (using the convention of 252 trading days a year).
- Across assets it is the other way round: a portfolio's simple return is the weighted average of its holdings' simple returns, but its log return is not the weighted average of their log returns.
Why. (1) $\log\frac{P_n}{P_0}=\log\Big(\frac{P_1}{P_0}\cdot\frac{P_2}{P_1}\cdots\frac{P_n}{P_{n-1}}\Big)$, and the log of a product is the sum of the logs — the product "telescopes". (2) is $\log y\le y-1$ with $y=1+R$. (3) follows from (1) applied to yearly or daily periods. (4) A portfolio with weights $w_j$ (summing to 1) in assets with simple returns $R_j$ grows by $\sum w_j(1+R_j)=1+\sum w_jR_j$; the log of a sum is not the sum of the logs. ∎
NPV, IRR and XIRR: Irregular Cash Flows and Decisions
CAGR needs one amount in and one amount out. Real life is messier: a SIP puts money in every month and a redemption takes some out; a rooftop solar system costs money now and saves money for fifteen years. The tool is the present value of §1.1, applied to every cash flow and added up.
A project has cash flows $C_0,C_1,\dots,C_n$ at the ends of periods $0,1,\dots,n$ (money paid out is negative, money received positive). At a rate $x\gt-1$ per period its net present value is $$\text{NPV}(x)=\sum_{k=0}^{n}\frac{C_k}{(1+x)^k}.$$ An internal rate of return (IRR) is a rate $x^*\gt-1$ with $\text{NPV}(x^*)=0$. The discount rate (hurdle rate, cost of capital) $k$ is the return available elsewhere on money of similar risk — what the money could earn, or what it costs to borrow.
For cash flows $C_0,\dots,C_m$ on dates $d_0\le d_1\le\cdots\le d_m$, put $t_k=(d_k-d_0)/365$ years. The XIRR is the annual rate $x$ that solves $$f(x)=\sum_{k=0}^{m}C_k\,(1+x)^{-t_k}=0.$$ For an investor, every purchase or SIP instalment is negative, every redemption or dividend received is positive, and the current value is entered as a final positive flow on the valuation date. This is what a spreadsheet's XIRR(values, dates) computes.
Suppose every outflow ($C_k\lt0$) happens no later than a time $a$, every inflow ($C_k\gt0$) happens after $a$, and there is at least one of each. Then $f$ (the NPV or XIRR function) has exactly one root $x^*\gt-1$, with $f(x)\gt0$ for $x\lt x^*$ and $f(x)\lt0$ for $x\gt x^*$. Consequently, at a discount rate $k\gt-1$: $$\text{NPV}(k)\gt0\iff x^*\gt k.$$ NPV rule: a project is worth doing if its NPV at the discount rate is positive; among mutually exclusive projects choose the one with the largest NPV. If outflows and inflows alternate, $f$ may have several roots or none, and only the NPV rule is reliable.
Proof. Multiplying $f$ by the positive number $(1+x)^{a}$ does not change its roots or signs, so study $g(x)=(1+x)^a f(x)=\sum C_k(1+x)^{a-t_k}$. An outflow term $-|C_k|(1+x)^{a-t_k}$ has exponent $\ge0$, so it is non-increasing in $x$. An inflow term $C_k(1+x)^{a-t_k}$ has a negative exponent, so it is strictly decreasing. Hence $g$ is strictly decreasing on $(-1,\infty)$. As $x\to-1^+$ the inflow terms tend to $+\infty$, so $g\to+\infty$; as $x\to\infty$ the inflow terms tend to 0, while for $x\ge0$ each outflow term is at most $-|C_k|$ (its exponent is $\ge0$), so $g$ is eventually negative. A continuous, strictly decreasing function that goes from positive to negative crosses zero exactly once (intermediate value theorem). ∎
Finding the IRR or XIRR. Except in special cases (Exercises III), $f(x)=0$ cannot be solved by algebra, so a spreadsheet solves it numerically. The standard method comes from calculus: replace the curve by its tangent line and see where the tangent crosses zero.
With $f(x)=\sum_k C_k(1+x)^{-t_k}$, its derivative is $$f'(x)=-\sum_{k}t_k\,C_k\,(1+x)^{-t_k-1},$$ and Newton's iteration from a starting guess $x_0\gt-1$ is $$x_{j+1}=x_j-\frac{f(x_j)}{f'(x_j)}.$$ If $f'(x^*)\ne0$ and $x_0$ is close enough to the root $x^*$, the iterates converge to $x^*$ and the number of correct digits roughly doubles at each step.
Why. Differentiate term by term: $\frac{d}{dx}(1+x)^{-t}=-t(1+x)^{-t-1}$. The tangent at $x_j$ is $y=f(x_j)+f'(x_j)(x-x_j)$, which is zero at $x_{j+1}$. Near the root, $f$ is almost a straight line, so the tangent's zero is a much better guess; the error after a step is roughly proportional to the square of the error before it (the proof uses Taylor's theorem). Far from the root, or where $f'(x)\approx0$, a step can overshoot wildly — try it in Interactive 1. When the only outflow is the first one, $f$ is decreasing and convex, and Newton started below the root climbs steadily to it. ∎
- List every cash flow with its date: each instalment as a negative amount, each redemption or payout received as a positive amount.
- Add one final positive flow: the current value of the units still held, on the valuation date.
- Convert dates to years from the first date: $t_k=(d_k-d_0)/365$.
- Start from a guess such as $x_0=10\%$ and iterate $x_{j+1}=x_j-f(x_j)/f'(x_j)$ until $f(x_j)$ is within a rupee of 0 (or use
=XIRR(values, dates, 0.1)). - Check: discount every flow at the answer and confirm that they sum to about zero. If the method fails, try a different guess, and look for alternating outflows and inflows (Theorem 1.15).
| $j$ | $x_j$ | $f(x_j)$ (₹) | $f'(x_j)$ (₹) |
|---|---|---|---|
| 0 | 10.0000% | 1,114.55 | −1,20,019 |
| 1 | 10.9286% | 14.14 | −1,16,992 |
| 2 | 10.9407% | 0.00 | −1,16,953 |
Meera invested ₹10,000 on the 5th of every month from January 2023 to December 2024 (24 instalments, ₹2,40,000). The hypothetical NAV of Fund A started at ₹50.00, dipped to about ₹45 in mid-2023 and recovered to ₹59.10 by the last instalment. She bought 4,693.616 units in all (an average cost of about ₹51.13 per unit — the harmonic mean of §2.2). On 12 August 2024 she redeemed ₹50,000 at an NAV of ₹56.40 (886.525 units). On 31 December 2024 the NAV is ₹60.20, so her 3,807.091 remaining units are worth ₹2,29,187.
- Absolute return. Gain $=2{,}29{,}187+50{,}000-2{,}40{,}000=$ ₹39,187, i.e. 16.33% of the money invested. But over what period? The first rupee was invested for two years, the last for less than a month.
- The colleague's "CAGR". Treating all ₹2,40,000 as invested on 5 Jan 2023 (1.989 years earlier): $(2{,}79{,}187/2{,}40{,}000)^{1/1.989}-1\approx7.90\%$. This understates the return, because most of the money was invested for much less than two years.
- XIRR. 24 outflows of ₹10,000, an inflow of ₹50,000 on 12 Aug 2024 and a final inflow of ₹2,29,187 on 31 Dec 2024. Newton from 10% gives 15.943% → 16.564% → 16.570%: XIRR ≈ 16.57% a year (Interactive 1, first case). Check: had each of her instalments instead earned an illustrative 7.25% a year in a deposit, with the same ₹50,000 withdrawal, she would have about ₹2,06,813 now — ₹22,000 less.
- XIRR vs the fund's own return. The fund's NAV went from ₹50.00 to ₹60.20 over the same 1.989 years: a point-to-point CAGR of only 9.78%. Meera's XIRR is higher because her SIP happened to buy many units during the 2023 dip. XIRR is a money-weighted return: it measures the investor's experience, including the timing of her flows. The fund's CAGR is time-weighted: it measures the fund. Another investor in the same fund, with different dates, would get a different XIRR.
The mathematics, not a recommendation. The honest summary is "my money earned about 16.6% a year over these two years, helped by a dip early on; the fund itself returned about 9.8% a year over the period, before tax". Neither number is a forecast, and both are pre-tax: gains on redemption may be taxed (check the current rules).
Rolling Returns and the Point-to-Point Trap
A factsheet's "3-year return" is computed from one start date to one end date. Move either date by a few months, across a crash or a rally, and the number can change completely. Rolling returns replace that single number by the whole distribution of returns over every window of the same length.
Let $N_s$ be a fund's NAV at date $s$. The point-to-point return over $w$ years ending at date $s$ is the CAGR $$\rho_w(s)=\left(\frac{N_s}{N_{s-w}}\right)^{1/w}-1.$$ When $s$ is today it is called the trailing $w$-year return. The rolling $w$-year returns are the values $\rho_w(s)$ for every end date $s$ in the history (daily, monthly or yearly steps), summarised by their minimum, median, maximum and the share of windows with a loss or above a chosen rate.
Rolling returns answer the question an investor actually faces: "if I had invested for $w$ years starting at a random date, what range of outcomes did this fund deliver?" A trailing return answers only "what happened from one particular date to today?" — and that date may have been chosen, consciously or not, because it flatters.
| end of Year | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
|---|---|---|---|---|---|---|---|---|
| 3-y CAGR % | 3.23 | −11.99 | −7.56 | 6.80 | 22.59 | 8.96 | 9.51 | 11.38 |
- "Average return = CAGR." The arithmetic mean is always at least the CAGR, by roughly $\sigma^2/2$ (Theorem 1.13). +40%, −25%, +30%, −10%, +15% "averages" 10% but compounds at 7.2% (Example 3).
- "Down 50%, up 50%, so I'm back to even." $0.5\times1.5=0.75$: you are down 25%. A 50% fall needs a 100% gain to recover; a 40% fall needs 66.7%.
- Annualising a short return. 3% in 10 days "is" 194% a year only if the luck repeats 36 times (Example 2). Report short-period returns as absolute returns.
- Judging a SIP by its absolute return, or by a CAGR on the total invested. Money entered at different dates; only the XIRR accounts for that (Market case: 7.9% "CAGR" vs 16.6% XIRR).
- "My XIRR beat the fund's return, so I picked well." XIRR is money-weighted: it reflects the timing of your flows (luck, here), not the fund's quality. Compare funds by time-weighted returns over the same period.
- "Higher IRR = better project." IRR ignores scale and can be non-unique when cash flows change sign more than once (Example 7). Use NPV at the right discount rate.
- Adding simple returns over time, or averaging log returns across stocks. Log returns add across time; simple returns average across assets (Theorem 1.14).
- Trusting one point-to-point number. A trailing return depends on its start date; a window that starts at a market bottom flatters (Example 8). Look at rolling returns — and remember that past performance, rolling or not, is not a forecast.
- Comparing returns that are not comparable. Different periods, pre-tax vs post-tax, before vs after costs, or a high-risk fund vs a deposit: a return number means little without its period, costs, taxes and risk (Chapters 3 and 8).
Exercises
All data are hypothetical. Use $t=\text{days}/365$ for dates, annual compounding for CAGR and XIRR, and $\log$ for the natural logarithm. Give rates to two decimal places.
- 1 An NAV rises from ₹120 to ₹186 in exactly 3 years. Find the absolute return and the CAGR.
- 2 ₹2,50,000 invested on 1 April 2019 is worth ₹4,10,000 on 15 September 2024. Find the CAGR.
- 3 A fund returned 5% in six months. Annualise it with compounding and without. How should a factsheet show it?
- 4 A 5-year CAGR of 11% corresponds to what total return?
- 5 An advertisement says a fund "doubled investors' money in 6 years". What CAGR is that?
- 6 What CAGR turns ₹5 lakh into ₹15 lakh in 12 years?
- 7 A share gains 4% in two weeks (14 days). Compute its annualised return and explain why the number should not be quoted.
- 1 Returns of +25% and −20% in two years: find the arithmetic mean, the CAGR and what ₹1 lakh became.
- 2 For yearly returns 12%, 8%, −6%, 18%, find $A$, $G$, $\sigma$ (population) and the approximation $A-\sigma^2/2$.
- 3 After a 40% fall, what gain is needed to get back to the starting value?
- 4 Closing prices ₹250, ₹262, ₹255, ₹270: find the three log returns and check that they add to $\log(270/250)$.
- 5 A stock's mean daily log return is 0.05%. Using 252 trading days, find the annual log return and the equivalent simple annual return.
- 6 A fund's log returns are 0.15 in Year 1 and −0.08 in Year 2. What is its simple return over the two years?
- 7 A portfolio is 50% in a stock that returns +30% and 50% in one that returns −10%. Compute the portfolio's simple return, its log return, and the (incorrect) weighted average of the two log returns.
- 1 Find the NPV at 10% of the cash flows −₹1,00,000 now, then ₹40,000, ₹50,000 and ₹30,000 at the ends of Years 1–3. Its IRR is about 10.13%; verify the sign of the NPV is consistent with this.
- 2 Should the project of part 1 be accepted if the discount rate is 12%? Compute the NPV.
- 3 Find the IRR of investing ₹80,000 now to receive ₹1,00,000 in exactly 2 years, by algebra.
- 4 ₹1,00,000 invested on 1 Jan 2025 is worth ₹1,08,000 on 1 Oct 2025. Find the XIRR.
- 5 For cash flows −100, +60, +60 at years 0, 1, 2, carry out one Newton step from $x_0=10\%$. (The IRR is about 13.07%.)
- 6 Find both IRRs of the cash flows −₹1,000, +₹2,500, −₹1,540 at years 0, 1, 2.
- 7 For the project of part 6, is the NPV at 8% positive or negative? Explain why "its IRR is 10%" is not a useful summary.
- 1 Find the yearly returns, their average, and the 8-year CAGR.
- 2 Find the six rolling 3-year CAGRs and their worst, median and best values.
- 3 Find the trailing 5-year CAGR at the end of Year 6 and at the end of Year 8.
- 4 A leaflet at the end of Year 6 shows "2-year return: 22.3% a year". Verify it and explain what makes it flattering.
- 5 In how many of the six 3-year windows did Fund E beat 8% a year?
- 6 Two funds have the same trailing 5-year return. Name three statistics from their rolling returns that would help you compare them, and say why.
E1. NISM-style The NAV of a growth option rises from ₹20 to ₹32 in exactly 4 years. Its compound annual growth rate is closest to (A) 15.00% (B) 12.47% (C) 60.00% (D) 11.75%.
E2. NISM-style An investor has made monthly SIP instalments for three years, with two partial redemptions in between. The most appropriate measure of the return she earned is (A) the absolute return (B) the CAGR from the first instalment date (C) the XIRR (D) the average of the fund's three calendar-year returns.
E3. NISM-style A portfolio gains 20% in one year and loses 20% the next. Over the two years the investor has (A) broken even (B) lost 4% (C) gained 4% (D) lost 20%.
E4. Case study A savings-cum-insurance plan (hypothetical) asks for ₹1,00,000 at the start of each of 5 years (Years 0–4) and pays ₹6,50,000 at the end of Year 10. (a) Find its IRR. (b) Find its NPV at an illustrative deposit rate of 7%. (c) What would the same five payments have grown to by Year 10 at 7%? (d) What else must be considered before comparing the two?
E5. Case study Two hypothetical funds had these yearly returns over the same six years. Fund C: +30, −15, +25, −5, +20, +10 (%). Fund D: +12, +8, +14, +6, +11, +9 (%). (a) Find each fund's average return and CAGR. (b) Find each fund's four rolling 3-year CAGRs. (c) Which fund turned ₹1 lakh into more, and what does that illustrate?
E6. Interview A fund's NAV is ₹100 today, falls to ₹50 after one year and returns to ₹100 after two years, so its point-to-point return is 0%. An investor puts ₹100 in today and ₹100 after one year. What is her XIRR after two years, and why is it not 0%?
Suggested plan — 2 lectures × 60 min.
- One number for a return. Open with the 📍 situation and take a show of hands: "Is Meera's return 8%, 16% or something else?" Do Examples 1–2 (CAGR, annualising), then Example 3 on the board: ask the class for the "average return" first, then compute what ₹1 lakh became. Prove Theorem 1.13 with the tangent-line inequality $\log y\le y-1$ — a two-line proof students remember. Log returns and Example 4: emphasise "across time add logs, across assets average simple returns".
- Cash flows, decisions and track records. NPV/IRR with the solar case (Example 5) and Theorem 1.15. Project Interactive 1: Example 6 case first, then Meera's case, then the two-IRR case started at 0%, 15% and 30%. Walk through the 📈 Market case. Finish with rolling returns: Example 8, then project Interactive 2 and let a student "choose the factsheet date" that makes the fund look best.
Class activity. Give each group a different (hypothetical) set of SIP dates, amounts and a final value. Each group computes the absolute return, the naive CAGR on the total and the XIRR (by two Newton steps and then with a spreadsheet), and explains on one slide which number to quote and why.
Misconceptions to watch for. Average return = CAGR; annualising a week's gain; "16% in two years = 8% a year" for a SIP; higher IRR always better; an XIRR above the fund's CAGR proves skill; a trailing return describes the fund's typical behaviour.
Suggested homework. I(1, 2, 5, 7), II(1, 2, 4, 6), III(1, 3, 5, 6), IV(1–4); Practice corner E1, E4, E6.
Project. (1) Build an XIRR sheet for a SIP — your own, a family member's, or an invented one — and check the spreadsheet's answer with your own Newton iteration. (2) Download a fund's or index fund's NAV history and compute its rolling returns.
Why it matters. Statements and apps show many different "returns". Once you can compute XIRR and rolling returns yourself, you can check any claim and compare funds on the same dates, instead of relying on whichever number is printed largest.
What to do.
- Cash-flow sheet. Column A dates, column B amounts (instalments negative, redemptions positive), last row = valuation date and current value (positive). Then
=XIRR(B2:B30,A2:A30,0.1). - Your own Newton. In C2 put
=(A2-$A$2)/365(years $t_k$). Put a guess in F1 (say 0.1). In D2=B2*(1+$F$1)^(-C2)and in E2=-C2*B2*(1+$F$1)^(-C2-1); copy down. Then=F1-SUM(D:D)/SUM(E:E)in F2 is the next guess; copy the guess into F1 and repeat three or four times. Compare with XIRR. - NAV history. Download daily NAVs of one growth-option fund from the AMFI website or the fund house's website, and keep one NAV per month-end (column A dates, column B NAV).
- Rolling returns. For 3-year windows with monthly data, in C38 put
=(B38/B2)^(1/3)-1and copy down. Summarise with=MIN(C:C),=MEDIAN(C:C),=MAX(C:C)and=COUNTIF(C:C,"<0")/COUNT(C:C); chart column C against the dates. - Averages. From the calendar-year-end NAVs compute the yearly returns, their
=AVERAGE(), the CAGR and=AVERAGE()-VAR.P()/2; compare (Theorem 1.13).
Python version: nav = df.set_index('date')['nav'].resample('M').last(), then roll3 = (nav / nav.shift(36))**(1/3) - 1 and roll3.describe(); for XIRR, use scipy.optimize.brentq on lambda x: sum(c*(1+x)**(-t) for c,t in flows).
Hand in: your cash-flow table with the XIRR from the spreadsheet and from your Newton steps; the rolling-return chart with min/median/max; and one paragraph on how different the fund looks from its trailing 3-year return.
Going further. Compute the fund's time-weighted return over your SIP period from its NAVs and compare it with your XIRR (Market case). Then compute rolling returns on log returns, $\log(N_s/N_{s-36})/3$, and check that they convert back with $e^r-1$.
🧪 Self-check quiz
Q1. A fund returns +50% in one year and −50% in the next. Its CAGR over the two years is about
Q2. XIRR is the rate $x$ that
Q3. Daily log returns of a share over a month are useful because
Q4. Two mutually exclusive projects: A has IRR 25% and NPV ₹40,000 at a 9% discount rate; B has IRR 15% and NPV ₹90,000. Following the NPV rule you choose
Q5. A fund's trailing 3-year return is 24% a year, measured from a market low. Rolling 3-year returns would