Register
Log In
Unanswered
Home
June 27, 2016
Offline
I have made a visual calculator where it calculates the future value of regular periodic investments. These monthly investment contributions (popularly known as SIP - Systematic Investment Plan) can also increase by a certain percentage every year. So if contributions increase by 10% every year means if first 12 installments are of 5000/month, next 12 investments will be of 5500/month and so on. Each and every investment will grow at certain assumed annual rate of return also. I found the future value through a detailed year-wise calculation. But I want this to be done with the help of a single formula so that back calculation can also be done. Looking forward to your kind help and suggestions. File is attached herewith.
July 16, 2010
Offline
Sorry for missing this question.
I don't think you can have one formula for all cells in the SIP table because your first FV formula has the type argument set to 1 (meaning payments are due at the beginning of the period), whereas your remaining formulas have type omitted, which is reverts to type 0 (meaning payments are due at the end of the period).
You could do something with helper cells to populate the rate and nper values so the formula referenced cells instead of having the calculation nested in the formula. This way the differences in your formulas would be performed outside of the formula itself.
Hope that makes sense and helps point you in the right direction.
Mynda
July 4, 2016
Offline
Hi,
Just so I have a handle on your situation, please confirm, correct or add to the following:
1 payments are made each month, into perpetuity (let’s call that 10 years for the sake of calculation). Therefore, payments made in the 1st year will be 60,000 (5,000 per mth), 2nd year 66,000 (5,500 per mth), 3rd year 72,600 (6,050 per mth), and so on. That makes the total amount invested in the order of 956,245.48.
2 the original years investment (60,000 + whatever interest is earnt) will form the basis of the starting PV for the next year
3 no deductions will be made over the years
4 interest will be paid monthly for each new year (ie – increased investment amount), but paid annually for subsequent years
Now, a couple of questions...
1 will interest be paid at the beginning of the period, or the end? Or is it different between each new year versus the previous year(s)? If different, why?
2 the calculation of the average annual return assumed for each new years amount is different to that of previous years. Is that due to an annual commission or fee? Is so, what is it / how is it defined?
For example
for each new year the calculation is ((1+Sheet1!$E$8)^(1/12)-1) = 0.9488792935% per period (month)
for subsequent years the calculation is (Sheet1!$E$8) = 12.00% per period (year)
If the method of calculation per period was to be equitable between new & previous years, the first calculation would be (Sheet1!$E$8/12) = 1.00% per period (month)
Let me know if anything is unclear.
cheers, Craig
June 27, 2016
Offline
Thanks Mynda and Craig for taking your valuable time out to help me.
Let me clarify:
1st year 12 instalments of Rs. 5000 each was invested at start of every month. I found the FV of these 12 instalments after 1 year using FV (Rate, Nper, PMT,,Type). As I have been given annual average return that is why I am writing Rate as ((1+Sheet1!$E$8)^(1/12)-1)
But after 1 year this particular FV becomes PV for subsequent years and the formula used is FV (Rate, Nper,,PV,)
So at the end of 2 years FV consists of
FV of first year end total value
+
FV of second year's 12 instalments of each Rs. 5,500.
This is a typical case of graduated annuities but with a difference as here monthly annuities change after every 12 months.
[Rs. is short form of Indian currency Rupees.]
June 27, 2016
Offline
I got some nice insights from here http://www.tvmcalcs.com/index......_annuities
May be you can also refer the above link to understand it better.
July 4, 2016
Offline
Hi amisandip.
I'm having trouble with a couple of things...
1/ why do you calculate the periodic interest rate for each new set of payments as (1+Sheet1!$E$8)^(1/12)-1 1.12^0.83333-1 = 0.00948879 (0.948879%)
Why not just use 0.12/12 (ie 1.0000%)?
As the FV calculated here flows on, any error is magnified. Therefore it must be correct.
2/ As Mynda states, you are mixing when interest is paid. For each new set of payments you want the interest paid at the beginning of the period (basically paying interest as soon as the payment is made). For subsequent years - using the already calculated FV - you want the interest paid at the end of the period. It doesn't make sense to me. Why would they not be the same? Are there more rules of which we should be aware?
I've done some calculations using interest paid at end of period (for all calculations) and the difference between your 10 year figure & mine is 10,753. Quite significant.
June 27, 2016
Offline
When finding FV of an annuity we are supposed to follow this i.e. RATE = (1+ Annual Return)^(1/12)-1 instead of (Annual Return / 12) as this gives more accurate result. Though the actual mathematical reason behind this if you ask me, I am clueless 🙁 You can also refer to this link http://msofficeworld.com/futur.....y-formula/
Most Users Ever Online: 57
Currently Online: azri adrin, Mark Lund, Rudi
19 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Ajay Mukhi
Stephen Motte
Rodney Hoyle
michael pitre
Samantha Burleson
Krishnaa Kurugod
David McSwain
Shameem Nassiree
Alanna Henderson
Janel Oz
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
