Does Smartsheet allow the calculation of IRR?
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 reallife program can share with me?.
Best Answers

Yes. But. This is the only way I have been able to figure out IRR in Smartsheet:
 Enter an IRR guesstimate in a cell in Smartsheet
 Enter the years 0 through whatever in Smartsheet cells horizontally
 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
 Copy the formula horizontally across all the years in your projection
 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
 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.

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.
Answers

Yes. But. This is the only way I have been able to figure out IRR in Smartsheet:
 Enter an IRR guesstimate in a cell in Smartsheet
 Enter the years 0 through whatever in Smartsheet cells horizontally
 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
 Copy the formula horizontally across all the years in your projection
 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
 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.

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.

No luck. I ended up getting a stack overflow. I will try to simplify the calculation. Bummer.
Help Article Resources
Categories
Check out the Formula Handbook template!