Question about calculating IRR on a rolling basis

Say the investment horizon is 3 years.

In year 1, you invest 4MM at 11c/share.

In year 2, you invest 3MM at 20c/share.

If the stock price by the end of year 2 A. falls to 10c/share or B. rises to 25c/share, what is your total return for your investment?

Seems like a rather easy question but can't wrap my head around it, thanks

 
Best Response

You just need to build the cashflow schedule and use the IRR function in Excel / your calculator.

In Period 1, you spent $4mm and purchased 36.4mm shares at 11c/share. In Period 2, you spent $3mm and purchased 15mm shares at 20c/share. Your total shares are 51.4mm

If the price goes to 10c/share, you will receive $5.14mm from selling. At 25c/share, you receive $12.8mm.

Your cashflows in the two cases are:

10c -$4mm, -$3mm, +$5.1mm = ~ -18% IRR

25c -$4mm, -$3mm, +$12.8mm = ~ 46% IRR

 
tucker_fitzpatrick:

You just need to build the cashflow schedule and use the IRR function in Excel / your calculator.

In Period 1, you spent $4mm and purchased 36.4mm shares at 11c/share. In Period 2, you spent $3mm and purchased 15mm shares at 20c/share. Your total shares are 51.4mm

If the price goes to 10c/share, you will receive $5.14mm from selling. At 25c/share, you receive $12.8mm.

Your cashflows in the two cases are:

10c
-$4mm, -$3mm, +$5.1mm = ~ -18% IRR

25c
-$4mm, -$3mm, +$12.8mm = ~ 46% IRR

Thanks a ton! Got it :)

 
tucker_fitzpatrick:

You just need to build the cashflow schedule and use the IRR function in Excel / your calculator.

In Period 1, you spent $4mm and purchased 36.4mm shares at 11c/share. In Period 2, you spent $3mm and purchased 15mm shares at 20c/share. Your total shares are 51.4mm

If the price goes to 10c/share, you will receive $5.14mm from selling. At 25c/share, you receive $12.8mm.

Your cashflows in the two cases are:

10c
-$4mm, -$3mm, +$5.1mm = ~ -18% IRR

25c
-$4mm, -$3mm, +$12.8mm = ~ 46% IRR

One last quick question, how would our IRR change if the time periods were 6 months instead of 1 year(so investments on month 1, month 7, etc)? Does that make a difference in the excel function?

 

The simple irr-function calculates in periods, i.e. every cell down with a number in it is one period. Word of advice, always use the xirr function if you are doing anything with specific dates. It gives you much more flexibility to insert cash flows, change time periods etc.

 

Asperiores cumque excepturi minus quis dolorem impedit repellat. Est molestias perferendis error in dolores. Consequatur quasi vel architecto ullam quis. Et sapiente sunt perferendis corporis. Voluptate et vitae molestias non.

Neque consequuntur quia recusandae et qui ab doloribus odio. Et sapiente harum vero libero dolores. Qui beatae est explicabo voluptatem. Voluptatem eveniet cum cupiditate vero eos.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
BankonBanking's picture
BankonBanking
99.0
4
Betsy Massar's picture
Betsy Massar
99.0
5
CompBanker's picture
CompBanker
98.9
6
dosk17's picture
dosk17
98.9
7
GameTheory's picture
GameTheory
98.9
8
DrApeman's picture
DrApeman
98.9
9
kanon's picture
kanon
98.9
10
bolo up's picture
bolo up
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”