Question description:
This user has given permission to use the problem statement for this
blog.
how would i do this question on excel?
An investment will generate £15,000 a year for 20 years. If you require a rate of compensation of
10% and the investment costs £100,000, show whether it is worth buying? If expected inflation is
instead estimated to be 2% higher, show how this impacts on your recommendation.
Solved by V. W. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
28/11/2017 - 11:55
hi
User
28/11/2017 - 11:55
hi
Excelchat Expert
28/11/2017 - 11:56
So let me get the query
Excelchat Expert
28/11/2017 - 11:56
You want to invest 15000 per year
Excelchat Expert
28/11/2017 - 11:57
investment cost is 100000 with 10% compensation
User
28/11/2017 - 11:57
yes
Excelchat Expert
28/11/2017 - 11:57
what is the last part? Is that another case?
User
28/11/2017 - 11:57
dont worry about the last part for now
Excelchat Expert
28/11/2017 - 11:59
100000 is the amount of cost after 20 years?
User
28/11/2017 - 11:59
yes
Excelchat Expert
29/11/2017 - 12:02
Can you see my works
User
29/11/2017 - 12:02
yeah i can
Excelchat Expert
29/11/2017 - 12:03
so In Future value I am calculating the amount you ill get after 20 years
Excelchat Expert
29/11/2017 - 12:03
For yearly earning of 15000 with compensation rate 10%
Excelchat Expert
29/11/2017 - 12:05
so based on the values it is worth buying
Excelchat Expert
29/11/2017 - 12:08
Another thing is important although
Excelchat Expert
29/11/2017 - 12:08
your cost is beginning from the initial period?
User
29/11/2017 - 12:09
why did the figure just change?
Excelchat Expert
29/11/2017 - 12:10
I changed it
Excelchat Expert
29/11/2017 - 12:10
The cost of 100000 is that going out each year?
User
29/11/2017 - 12:10
no
User
29/11/2017 - 12:10
just at the start
Excelchat Expert
29/11/2017 - 12:11
hmm ok, wait
Excelchat Expert
29/11/2017 - 12:17
now check
Excelchat Expert
29/11/2017 - 12:17
We have a cost of 100000 at the beginning which we can say our present value
Excelchat Expert
29/11/2017 - 12:17
PV
Excelchat Expert
29/11/2017 - 12:18
This PV with a rate of 10% per year will have a future value of 672,750...after 20 years
User
29/11/2017 - 12:21
i see
Excelchat Expert
29/11/2017 - 12:21
so based on the values the investment seems very risky
Excelchat Expert
29/11/2017 - 12:23
the only catch here is is the discount rate here means the interest rate for investment or not
Excelchat Expert
29/11/2017 - 12:26
so ok?
User
29/11/2017 - 12:27
how did you get 672749?
Excelchat Expert
29/11/2017 - 12:27
The formula is of FV(future value)
Excelchat Expert
29/11/2017 - 12:28
FV=PV(1+r)^n.....if I am correct with the formula
Excelchat Expert
29/11/2017 - 12:28
PV=present investment
Excelchat Expert
29/11/2017 - 12:28
r=rate, n=no of period
Excelchat Expert
29/11/2017 - 12:28
FV is the how much this investment worths after 20 years
Excelchat Expert
29/11/2017 - 12:29
And in excel, we just give input to these values
Excelchat Expert
29/11/2017 - 12:32
Ok?
User
29/11/2017 - 12:33
yes i understand
Excelchat Expert
29/11/2017 - 12:33
So anything else I can help you with
User
29/11/2017 - 12:33
what would happen if inflaation was 2%?
Excelchat Expert
29/11/2017 - 12:34
let me think of that
Excelchat Expert
29/11/2017 - 12:38
Okay what I have found so far is the compensation rate is not the IR rather IR means in somewhat the inflation
Excelchat Expert
29/11/2017 - 12:39
So without inflation the scenario would be some what IR=0
Excelchat Expert
29/11/2017 - 12:40
now the compensation rate here is the main question
Excelchat Expert
29/11/2017 - 12:41
without inflation your present 100000 will worth same after 20 years which is actually make sense
User
29/11/2017 - 12:42
what do you mean?
Excelchat Expert
29/11/2017 - 12:42
It means suppose you keep some money in bank and the bank doesnt provide you any interest
Excelchat Expert
29/11/2017 - 12:43
so after 20 years what you will get is the same amount of money you kept in the bank
User
29/11/2017 - 12:43
that changes because of the inflation?
Excelchat Expert
29/11/2017 - 12:44
In banking sense that is interest rate( the extra amount you will be paid as a matter of time )
Excelchat Expert
29/11/2017 - 12:44
And in economic sense that will be inflation which here means that the same money you have today will worth less in the future
Excelchat Expert
29/11/2017 - 12:45
to sum up, with 2% inflation 100$ this year will be same as 102 $ next year
Excelchat Expert
29/11/2017 - 12:46
you got this part?
User
29/11/2017 - 12:46
yes
Excelchat Expert
29/11/2017 - 12:47
so with no inflation what you invested today will worth same after 20 years
Excelchat Expert
29/11/2017 - 12:47
But if there is inflation the value will increase with positive inflation(inflation can be negative too)
Excelchat Expert
29/11/2017 - 12:48
which means that if you invest 100 this year with 2% inflation next year this investment was worth of 102 $ actually and so on
Excelchat Expert
29/11/2017 - 12:50
Now check one thing your earning is constant 15000 no matter what the inflation is, as that is fixed during the contract, so if your payment this year is 15000 and you have contracted to get this amount throughout 20 years it will not change
User
29/11/2017 - 12:52
okay
Excelchat Expert
29/11/2017 - 12:52
And I think the rate of compensation is if your ultimate profit is negative, your loss will be kept upto 10% below your investment by the contract, means if you invest 100 and get back 85 , then another 5$ will be paid back to you as compensation contract
Excelchat Expert
29/11/2017 - 12:54
did these help you? I hope so!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
