Question I have:
In order to do an impairment test for around 1000 business activities I do have to calculate the following:
Total amount of revenues per year over a period, e.g. 10 years, with fixed assumed increase of revenue per year, e.g 2%
Total amount of cost per year over a period, e.g. 10 years, with a fixed assumed increase of costs per year, e.g. 3% per year
Calculate with NPV over net result per year, e.g. 10 values, 5% discount interest rate
Let us asume revenues first year =10,000 with anual increase of 2% for 10 years
Let us asume costs first year =5,000 with anual increase of 3% for 10 years
Then what is the DCF of this values with an assumed interest rate of 5%
The NPV = 41,941 (which is the NPV for the values by year @ 5% interest rate). See the sample below and in attachment.
Now this works if plotted in an Excel sheet. Compound interest formula for the revenues and the costs and a NPV formula for the anual result.
However is there a way to do with help of VBA w/o plotting this in excel.
This will save me a lot of time (setting up around a 1000 sheets!)
Tnx in advance.
Year 2% 2% 5%
1 10.000 5.000 5.000
2 10.200 5.100 5.100
3 10.404 5.202 5.202
4 10.612 5.306 5.306
5 10.824 5.412 5.412
6 11.041 5.520 5.520
7 11.262 5.631 5.631
8 11.487 5.743 5.743
9 11.717 5.858 5.858
10 11.951 5.975 5.975
NPV= 41.940,53
Bookmarks