I've been told it's too difficult.

I've been told it's too difficult.

Didn't find your answer?

Giday UK.

'A' contains a month & year, ie: Nov 2001

'B' contains a random value, ie: 320,000 (Three hundred & twenty thousand,)

I require 'C' to recognize the year in 'A' and ascertain how many years until January 2010. (Using the above example would be 9 years). Then multiply the value in 'B' by the number of years, compounding at 7% per year. Using the example the end value in 'C' will be 588,306.91

I will be happy for the formula to identify the year as a whole but even happier if it can identify the month as a decimal, ie: 1 month = .83 Therefore the above example would mean there are 8.083 years required for the calculation.

Cheers, ScottyT   

Replies (9)

Please login or register to join the discussion.

avatar
By ElliottRoss
25th Feb 2010 12:32

Is it always...
......7% and to Jan 2010 or would these need to be variable to?

Regards
Elliott

Thanks (0)
avatar
By ElliottRoss
25th Feb 2010 12:40

If the answer is no, they are fixed, then your answer is....
=B1*(1+0.07)^((40179-A1)/365)

Which equals = £556,278

Regards
Elliott
www.recenseo.co.uk

Thanks (0)
avatar
By User deleted
25th Feb 2010 15:04

Sorry to ask this.....

but where does the number 40179 come from?

Thanks (0)
avatar
By IanBrewster
25th Feb 2010 15:10

40179

Is 1st Jan 2010

could also use DATE(2010, 1, 1)

Thanks (0)
avatar
By ScottyT
26th Feb 2010 01:02

It did and didn't work.....

Hi Elliot,

Your formula works on a clean 'A' 'B' 'C' coloumed sheet but I may have made a mistake by giving you that example purely for simplistic purposes. The actual worksheet has 'A' through 'G' containing data where 'F' is to be used for your formula. Below is a pasted copy of the first 2 rows where I have simply replaced your 'B1' & 'A1' cells for 'D2' & 'C2' respectively. As you can see 'F' has produced an obscure datelike answer .

In case the paste doesn't appear the cell details are as follows:

'A2'- 180 Bay Tce, 'B2'- 405, 'C2'- 1/02/2009, 'D2'- 495,000, 'E2'- 1,222, 'F2'- 26/10/3341, 'G2'- 1,478,889

'A3'- 175 Bay Tce, 'B3'- 607, 'C3'- 1/08/2009, 'D3'- 1,200,000, 'E3'- 1,977, 'F3'- 30/12/5279, 'G3'- 2,392,092

A= Address, B= Size in m2, C= Sale date, E= Sale price, F= Ajusted current market value @7% compounding per year, G= Value of 1210m2 based on last purchase price.

I hope this will help. Thankyou for your efforts thus far.

Cheers, ScottyT

Thanks (0)
avatar
By ElliottRoss
26th Feb 2010 08:49

It's the format of you cell
Scotty

You have the cell set to a "Date" format, rather than a "Number" or "Currency". If you change this, you can see the answer you are after.

Regards
Elliott

Thanks (0)
avatar
By paulwakefield1
26th Feb 2010 08:55

Format

Reformat Column F. You have the column formatted as a Date format - change it to a numeric format. e.g. Format, cells, Number tab, Number (there are various other options and settings to get it to look the way you want it to).

 

Elliott has beaten me to it!

Thanks (0)
avatar
By ScottyT
26th Feb 2010 11:17

WE HAVE A WINNER!!

Thankyou Elliott. I owe you one. And thanks Paul for the attempted backup. Elliott was just too quick. :)

Cheers, ScottyT

 

Thanks (0)
avatar
By kerrym
13th Mar 2010 09:22

Use the analysis toolpak

While the formula given works approximately, I prefer to use the date tools in the analysis toolpak.

e.g. (1 + rate) ^ yearfrac(date1, date2) 

The options of yearfrac can be used to ensure that leap years are calculated correctly or even monthly compounding rather than daily.

Cheers, Kerry.

Thanks (0)