Does Smartsheet allow the calculation of IRR?

Michael Mather

I have found a key element of Program Management that needs solution in the Smartsheet platform. The calculation for IRR is critical component and not available as general function. As you may know, this simple one varying net cash flow typical program. Does anyone calculating real-life program can share with me?.

Jeff Hoelzel

Yes. But. This is the only way I have been able to figure out IRR in Smartsheet:

1. Enter an IRR guesstimate in a cell in Smartsheet
2. Enter the years 0 through whatever in Smartsheet cells horizontally
3. Enter a formula under each year like this: =free cash flow (for corresponding year from your projections) / (1 +IRR guesstimate (lock this cell) ^ (corresponding) year
4. Copy the formula horizontally across all the years in your projection
5. Sum the values of your calculation across all the years in your projection. This will give you the net present value of future cash flows
6. Change your IRR guesstimate until the NPV of future cash flows equals $0

The discount rate is the internal rate of return when the NPV of future cash flows is $0

I usually set this up in Excel and use goal seek plus the IRR formula for testing before posting in Smartsheet. If you need a cheat sheet, let me know and I'll send you one.

Michael Mather

Hello Jeff, That is an interesting approach. Basically, use the NPV equation then allow the user to manually load estimated IIR and interpolate that rate of return until they get to an NPV = 0. I will try that.

Jeff Hoelzel

Yes. But. This is the only way I have been able to figure out IRR in Smartsheet:

1. Enter an IRR guesstimate in a cell in Smartsheet
2. Enter the years 0 through whatever in Smartsheet cells horizontally
3. Enter a formula under each year like this: =free cash flow (for corresponding year from your projections) / (1 +IRR guesstimate (lock this cell) ^ (corresponding) year
4. Copy the formula horizontally across all the years in your projection
5. Sum the values of your calculation across all the years in your projection. This will give you the net present value of future cash flows
6. Change your IRR guesstimate until the NPV of future cash flows equals $0

The discount rate is the internal rate of return when the NPV of future cash flows is $0

I usually set this up in Excel and use goal seek plus the IRR formula for testing before posting in Smartsheet. If you need a cheat sheet, let me know and I'll send you one.

Michael Mather

Hello Jeff, That is an interesting approach. Basically, use the NPV equation then allow the user to manually load estimated IIR and interpolate that rate of return until they get to an NPV = 0. I will try that.

Michael Mather

No luck. I ended up getting a stack overflow. I will try to simplify the calculation. Bummer.