Hi Everyone,
Sorry to bring up an old topic, but I am trying to recreate a simple revenue model that has hard coded value! I have been able to recreate everything but I am having a hard time getting the Pay Back period and a cumulative NPV to tie. The numbers I am looking to tie are in cells D61, D62,G62, D82, D83, G83.
Sorry to bring up an old topic, but I am trying to recreate a simple revenue model that has hard coded value! I have been able to recreate everything but I am having a hard time getting the Pay Back period and a cumulative NPV to tie. The numbers I am looking to tie are in cells D61, D62,G62, D82, D83, G83.
| Revenue Model.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 58 | Pre-Tax Cash Flow | Units | (5,082,616) | 1,711,691 | 855,372 | 682,953 | 618,424 | 735,892 | 888,459 | 730,316 | |||||
| 59 | Cumulative Cash Flow | (5,082,616) | (3,370,925) | (2,515,552) | (1,832,599) | (1,214,176) | (478,284) | 410,175 | 1,140,491 | ||||||
| 60 | Cumulative IRR | 9.19% | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | ||||||
| 61 | Cumulative NPV | - | 224,605 | ||||||||||||
| 62 | Payback | - | 6.54 | (3,497,717) | |||||||||||
| 63 | |||||||||||||||
| 64 | Tax | Units | |||||||||||||
| 65 | Investment Tax Credit | ||||||||||||||
| 66 | ITC | $ | 1,524,785 | ||||||||||||
| 67 | |||||||||||||||
| 68 | Federal Tax Benefit/(Liability) | ||||||||||||||
| 69 | Income Tax Liability Before Depreciation | ||||||||||||||
| 70 | Taxable Income | 1,711,691 | 855,372 | 682,953 | 618,424 | 735,892 | 888,459 | 730,316 | |||||||
| 71 | Tax Liability Before Depreciation | (359,455) | (179,628) | (143,420) | (129,869) | (154,537) | (186,576) | (153,366) | |||||||
| 72 | |||||||||||||||
| 73 | Accelerated Depreciation | ||||||||||||||
| 74 | Depreciable Basis After ITC | 4,320,223 | |||||||||||||
| 75 | Depreciation Schedule (7-yr MACRS) | 100.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | ||||||
| 76 | Depreciation | 4,320,223 | - | - | - | - | - | - | - | ||||||
| 77 | Tax Benefit from Depreciation | 907,247 | - | - | - | - | - | - | - | ||||||
| 78 | |||||||||||||||
| 79 | After-Tax Cash Flow | Units | (2,650,584) | 1,352,236 | 675,744 | 539,533 | 488,555 | 581,355 | 701,883 | 576,949 | |||||
| 80 | Cumulative Cash Flow | (2,650,584) | (1,298,348) | (622,604) | (83,071) | 405,484 | 986,838 | 1,688,721 | 2,265,670 | ||||||
| 81 | Cumulative IRR | 23.59% | #NUM! | #NUM! | #NUM! | #NUM! | -25.8% | -8.2% | 2.6% | ||||||
| 82 | Cumulative NPV | - | 1,542,121 | ||||||||||||
| 83 | Payback | - | 4.17 | $ (1,398,514) | |||||||||||
| 84 | |||||||||||||||
Revenue Model | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F58:M58 | G58 | =G56+G35 |
| G59,G80 | G59 | =SUM(F58:G58) |
| H59,H80 | H59 | =SUM(F58:H58) |
| I59,I80 | I59 | =SUM(F58:I58) |
| J59,J80 | J59 | =SUM(F58:J58) |
| K59,K80 | K59 | =SUM(F58:K58) |
| L59,L80 | L59 | =SUM(F58:L58) |
| M59,M80 | M59 | =SUM(F58:M58) |
| G60:M60 | G60 | =IRR($F$59:G59) |
| F59,F80 | F59 | =F58 |
| C60,C81 | C60 | =IRR(F58:Z58) |
| C61,C82 | C61 | =Z61 |
| C62 | C62 | =L62 |
| F66 | F66 | =F56*C5*-1 |
| G70:M70 | G70 | =G58 |
| G71:M71 | G71 | =(G$70*$C$6)*-1 |
| F74 | F74 | =-$F$56*0.85 |
| H75:M75 | H75 | =G75 |
| F76 | F76 | =F74 |
| F77 | F77 | =F74*C6 |
| G79:M79 | G79 | =SUM(G70:G71) |
| G81:M81 | G81 | =IRR($F$80:G80) |
| F79 | F79 | =SUM(F58,F66,F77) |
| C83 | C83 | =J83 |
