I am attempting to back into the sales number of $2.75MM by using the NPV and IRR that have been generated on yearly cash flows. I would like to be able to say that at a certain IRR and a NPV of "0" my sales would be "X" given the certain amount of costs over a given period of time. The biggest problem is two of the costs are formulated via a percentage of the sales price. Below is an example; the yellow cell is the one I am trying to create based off of a 20% IRR, the blue numbers are the ones that are generated via direct percentages or indirectly of the sales number. I have tried everything I can think of and have spent days and tried everything Excel seems to offer. Unfortunately the Solver function does not work because this is a dynamic sheet and I will need to back into 36 different iterations testing various IRR percentages.
| Book1 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | EndofPeriod | 2,005 | 2,006 | 2,007 | ||||
| 2 | PeriorofTime | 2 | Years | |||||
| 3 | LoanProceeds | 1,137,500 | - | - | ||||
| 4 | SalesRevenue | 2,750,000 | ||||||
| 5 | ||||||||
| 6 | interestincome@perannumrateof | 20.00% | 122,500 | 147,000 | 176,400 | |||
| 7 | accruetoprincipal | (122,500) | (147,000) | (176,400) | ||||
| 8 | CostofSales | 1,955,625 | 182,875 | 1,183,875 | ||||
| 9 | SellingCommission | 7.00% | 192,500 | |||||
| 10 | TotalSalesCosts | 1,955,625 | 182,875 | 1,376,375 | ||||
| 11 | ||||||||
| 12 | ||||||||
| 13 | GrossProfit | (818,125) | (182,875) | 1,373,625 | ||||
| 14 | GeneralManagerFee | 30.00% | 111,788 | |||||
| 15 | InitialInvestment | 612,500 | (818,125) | (182,875) | 1,261,838 | |||
| 16 | ||||||||
| 17 | newprincipalbalanceaccruinginterest | 612,500 | 735,000 | 882,000 | 1,058,400 | |||
| 18 | IRR | 13.52% | ||||||
| 19 | monthlygrowthrateofinvestment | 1.67% | ||||||
| 20 | annualgrowthrateofinvestment | 20.00% | ||||||
| 21 | presentvalueofinvestmentat | 20.00% | ($78,537.33) | |||||
Sheet1 | ||||||||
