**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.*