I've been told it's too difficult.
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
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
WE HAVE A WINNER!!
Thankyou Elliott. I owe you one. And thanks Paul for the attempted backup. Elliott was just too quick. :)
Cheers, ScottyT
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.
Is it always...
......7% and to Jan 2010 or would these need to be variable to?
Regards
Elliott