Response Part #2
I am responding in multiple parts to avoid posting limits.
I am not varrying the discount rate, I am actually varying the the royalty rate that applies to the top line revenues and using a constant discount rate
So you need to specify the discount rate to use. I assume that is in B7.
One solution I have come up with (only a slight improvement) is that I can output a data table that varies the input royalty % (starts at 1% and increase by 0.01% up to 3.00%), and then combine that with a MROUND function so that I can vlookup up from the data table and return the answer. [....] Any more thoughts?
I am not quite sure what you have in mind. I cannot comment without details. Please upload an Excel file that demonstrates this solution to a file-sharing website and post the public/share URL in a (new) response here.
My guess(es)....
In the real world, it makes sense for the royalty rate to depend on the sales revenue. (Or the number of units sold.)
For example, the royalty rate might be the base rate plus 0.01% for every $35 over $1200 up to $8200 (a max of 3%). That seems to be what you describe.
Ostensibly, the formula is: baseRate + MAX(0, MIN(2%, 0.01%*ROUNDUP((D4-1200)/35,0))), where D4 is sales revenue.
More generally, we might use LOOKUP to create more arbitrary royalty tiers, for example:
baseRate + LOOKUP(D4,{0,1200,1400,1800,2600,4200,7400,13800},{0,0.0001,0.001,0.0025,0.005,0.0075,0.01,0.02})
Alternatively, we might put the corresponding values into R1:R8 and S1:S8, for example, and use: baseRate + LOOKUP(D4, $R$1:$R$8, $S$1:$S$8).
-----
The mathematical formula for the NPV on date "d0" and given discount rate "r" could be written as follows:
NPV = Sigma(C
/(1+r)^((d-d0)/365), i=1,...,n)
Note some "labeling" differences with the Excel XNPV help page.
In your case, C = sales * (baseRate + x), where "x" is one of the incremental formulas above.
So, using the LOOKUP formula above, we can solve for baseRate as follows:
Rich (BB code):
NPV = Sigma(sales * (baseRate + LOOKUP(sales,R1:R8,S1:S8)) / (1+r)^((d-d0)/365), i=1,...,n)
= baseRate * Sigma(sales / (1+r)^((d-d0)/365), i=1,...,n)
+ Sigma(sales * LOOKUP(sales,R1:R8,S1:S8) / (1+r)^((d-d0)/365), i=1,...,n)
baseRate = (NPV - Sigma(sales * LOOKUP(sales,R1:R8,S1:S8) / (1+r)^((d-d0)/365), i=1,...,n))
/ Sigma(sales / (1+r)^((d-d0)/365), i=1,...,n)
The following demonstrates how we might implement this in Excel.
You can also download "rev stream.xls" (click here) [1]. See the "LOOKUP" and "ROUNDUP" worksheets.
[1] https://app.box.com/s/0efidwzemhln4h8a20t22j4l2s243gqd
LOOKUP method....
| A
| B
| C
| D
| E
| F
| G
| H
| I
| J
| K
| L
| M
| N
| O
| P
| Q
| R
| S
|
2
| CF#
|
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 |
| Sales | +Royalty |
3
| Date |
| 1/19/2016 | 6/30/2016 | 6/30/2017 | 6/30/2018 | 6/30/2019 | 6/30/2020 | 6/30/2021 | 6/30/2022 | 6/30/2023 | 6/30/2024 | 6/30/2025 | 6/30/2026 | 6/30/2027 | 6/30/2028 |
| 0 | 0.00% |
4
| Sales rev |
|
| 4,756 | 3,910 | 2,156 | 4,672 | 1,389 | 1,362 | 2,164 | 1,278 | 4,540 | 3,413 | 3,205 | 2,996 | 3,577 |
| 1,200 | 0.01% |
5
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 1,400 | 0.10% |
6
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 1,800 | 0.25% |
7
| NPV rate | 8.00% |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 2,600 | 0.50% |
8
| Reqd NPV | 400 | 450 | 500 | 550 | 600 | 650 |
|
|
|
|
|
|
|
|
|
| 4,200 | 0.75% |
9
| Base royalty | 1.0489% | 1.2454% | 1.4419% | 1.6384% | 1.8349% | 2.0313% |
|
|
|
|
|
|
|
|
|
| 7,400 | 1.00% |
10
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 13,800 | 2.00% |
11
| Royalty rate |
|
| 1.7989% | 1.5489% | 1.2989% | 1.7989% | 1.0589% | 1.0589% | 1.2989% | 1.0589% | 1.7989% | 1.5489% | 1.5489% | 1.5489% | 1.5489% |
|
|
|
12
| Royalty rev |
|
| 86 | 61 | 28 | 84 | 15 | 14 | 28 | 14 | 82 | 53 | 50 | 46 | 55 |
|
|
|
13
| NPV |
|
| 400 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<tbody>
</tbody>
Rich (BB code):
Formulas:
B9: {=(B8 - SUM($D$4:$P$4 * LOOKUP($D$4:$P$4,$R$3:$R$10,$S$3:$S$10) / (1+$B$7)^(($D$3:$P$3-$C$3)/365)))
/ SUM($D$4:$P$4 / (1+$B$7)^(($D$3:$P$3-$C$3)/365))}
Copy B9 into C9:G9
D11: =$B$9 + LOOKUP(D4,$R$3:$R$10,$S$3:$S$10)
Copy D11 into E11:P11
D12: =D4*D11
Copy D12 into E12:P12
D13: {=SUM(IF(COLUMN($C$3:$P$3)=$C$3,-B8,C12:P12) / (1+$B$7)^(($C$3:$P$3-$C$3)/365))}
Formulas displayed with curly brackets {...} are array-entered, to wit: type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.
The derivation of the base royalties for various required NPVs is in rows 8 and 9.
Rows 11 through 12 demonstrate the correctness of the base royalty in B9 for the required NPV in B8, for example.
We could use XNPV instead of SUM in D13, since the discount rate (B7) is positive, in this case.
ROUNDUP method:
| A
| B
| C
| D
| E
| F
| G
| H
| I
| J
| K
| L
| M
| N
| O
| P
|
2
| CF#
|
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 |
3
| Date
|
| 1/19/2016 | 6/30/2016 | 6/30/2017 | 6/30/2018 | 6/30/2019 | 6/30/2020 | 6/30/2021 | 6/30/2022 | 6/30/2023 | 6/30/2024 | 6/30/2025 | 6/30/2026 | 6/30/2027 | 6/30/2028 |
4
| Sales rev |
|
| 4,756 | 3,910 | 2,156 | 4,672 | 1,389 | 1,362 | 2,164 | 1,278 | 4,540 | 3,413 | 3,205 | 2,996 | 3,577 |
5
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
7
| NPV rate | 8.00% |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
8
| Reqd NPV | 400 | 450 | 500 | 550 | 600 | 650 |
|
|
|
|
|
|
|
|
|
9
| Base royalty | 0.8751% | 1.0715% | 1.2680% | 1.4645% | 1.6610% | 1.8575% |
|
|
|
|
|
|
|
|
|
10
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
11
| Royalty rate
|
|
| 1.8951% | 1.6551% | 1.1551% | 1.8751% | 0.9351% | 0.9251% | 1.1551% | 0.9051% | 1.8351% | 1.5151% | 1.4551% | 1.3951% | 1.5551% |
12
| Royalty rev
|
|
| 90 | 65 | 25 | 88 | 13 | 13 | 25 | 12 | 83 | 52 | 47 | 42 | 56 |
13
| NPV
|
|
| 400 |
|
|
|
|
|
|
|
|
|
|
|
|
<tbody>
</tbody>
Rich (BB code):
Formulas:
B9: {=(B8 - SUM($D$4:$P$4 * IF($D$4:$P$4<1200, 0, IF($D$4:$P$4>8200, 2%, 0.01%*ROUNDUP(($D$4:$P$4-1200)/35,0))) / (1+$B$7)^(($D$3:$P$3-$C$3)/365)))
/ SUM($D$4:$P$4 / (1+$B$7)^(($D$3:$P$3-$C$3)/365))}
Copy B9 into C9:G9
D11: =$B$9 + MAX(0, MIN(2%, 0.01%*ROUNDUP((D4-1200)/35,0)))
Copy D11 into E11:P11
D12: =D4*D11
Copy D12 into E12:P12
D13: {=SUM(IF(COLUMN($C$3:$P$3)=$C$3,-B8,C12:P12) / (1+$B$7)^(($C$3:$P$3-$C$3)/365))}
Note that we cannot use MAX and MIN in the array-entered formula in B9.
Alternatively, we could ensure that the sales revenue in row 4 is between 1200 and 8200. That obviate the need for the IF(...,0,IF(...,2%,...)) construct.
Any questions? 
