Okay so I've hit a bit of a road block on a pretty complicated financial spreadsheet, and could use some help.
At the very least I want to check to see if at least either of the pages are correct. I can test that if I could actually do a Future Value of Uneven Payments calculation preferably where I just highlight a table of numbers, highlight a table of years, and assign an interest rate and it runs a time value of each individual number for the remainder of the number of years and then adds together all of the future values for each one into a single future value number.
If someone really wanted to help me out this is the 2 calculations that I'm doing.
1) I have a set of future values down a spreadsheet(those are correct) than in this instance are rising very fast(1st year double, 2nd year increase by half, 3rd year increase by 1/4, etc.). I need to solve for PV and then let's say 39 nears for it to grow to that future value with no additional pmts (easy). Hard: The following year the future value is higher, I need solve another PV for that year while taking into account what has already been set aside the year prior and I need that combined value to increase to the future value on the spread sheet. The year after that yet again the future value in 37 years is even higher yet. I need to once again solve for a PV for that year that also takes into account the growth of the last 2 years lower payments and then those grow into the higher future value in 37 years. This continues until all 40 years are used up.
The way I tried to go about this was to:
A) Discount back that future value by 40 years and arrive at a present value for year 1(easy)
B) Discount back the next one by 39, the next by 38, the next by 37, etc.
C) And then I took that present value and subtracted (previous contributions*(1+rate)--i.e. in year 1 I let's say set aside $4k. The next year I need $9.5k so I increase the $4k by interest rate for 1 year and then added the difference. I then carried that all the way down the table where the next one is maybe $16k minus ($9.5k + interest). What doesn't look right is that B and the side column for the past contributions increased by 1 years interest are identical. And the values look smaller than they should be(at least the calculation in 2 below should produce lower numbers and it shows higher numbers).
2) In this calculation the future values on the table start higher and are growing very slowly(by just under 3% a year). Also I need to then assume that instead of 1 payment and no more for the remainder of each term that instead the payment is paid for the remainder of the 40 years. But since the next years future value is ~3% higher it needs to take into account the previous years payment(equal to 1 of 40 payments) and then slightly adjust to a higher future value.
The way I tried to go about this was to:
A) Solve for PMT(as opposed to PV) of the future value
B) In a second column take that 1 payment and calculate the future value of that for the remainder of the 40 years
C) In the following PMT calculation, calculate the pmt of using time value of 39 years(as opposed to 40) and subtracting the future value of the second column from the future value that needs to be solved for(i.e. 1 payment maybe grew from $8k to $40k in 40 years and so if you subtract from maybe the $830k in 40 years you end up solving what the PMT is for $790k in 39 years.
Both calculation 1 and 2 have the same future value in the 40th year. The problem is that 1 should be more exponential than 2 and 2 should be a lot flatter in the PMTs for each year. The problem is that 2 is ending up higher across the board so I know at least 1 of the methodologies I used above is flawed which is really causing me to scratch my head because they both seem like the logical way to proceed.
If anybody can identify my mistake I would really appreciate it. I'm new here so I don't know how to attach the excel spreadsheet for you guys to look at. Also if you can just tell me how to calculate the future value of uneven payments than I should at least be able to test each of them and know which one is wrong(if not both).
Thanks in advance!
At the very least I want to check to see if at least either of the pages are correct. I can test that if I could actually do a Future Value of Uneven Payments calculation preferably where I just highlight a table of numbers, highlight a table of years, and assign an interest rate and it runs a time value of each individual number for the remainder of the number of years and then adds together all of the future values for each one into a single future value number.
If someone really wanted to help me out this is the 2 calculations that I'm doing.
1) I have a set of future values down a spreadsheet(those are correct) than in this instance are rising very fast(1st year double, 2nd year increase by half, 3rd year increase by 1/4, etc.). I need to solve for PV and then let's say 39 nears for it to grow to that future value with no additional pmts (easy). Hard: The following year the future value is higher, I need solve another PV for that year while taking into account what has already been set aside the year prior and I need that combined value to increase to the future value on the spread sheet. The year after that yet again the future value in 37 years is even higher yet. I need to once again solve for a PV for that year that also takes into account the growth of the last 2 years lower payments and then those grow into the higher future value in 37 years. This continues until all 40 years are used up.
The way I tried to go about this was to:
A) Discount back that future value by 40 years and arrive at a present value for year 1(easy)
B) Discount back the next one by 39, the next by 38, the next by 37, etc.
C) And then I took that present value and subtracted (previous contributions*(1+rate)--i.e. in year 1 I let's say set aside $4k. The next year I need $9.5k so I increase the $4k by interest rate for 1 year and then added the difference. I then carried that all the way down the table where the next one is maybe $16k minus ($9.5k + interest). What doesn't look right is that B and the side column for the past contributions increased by 1 years interest are identical. And the values look smaller than they should be(at least the calculation in 2 below should produce lower numbers and it shows higher numbers).
2) In this calculation the future values on the table start higher and are growing very slowly(by just under 3% a year). Also I need to then assume that instead of 1 payment and no more for the remainder of each term that instead the payment is paid for the remainder of the 40 years. But since the next years future value is ~3% higher it needs to take into account the previous years payment(equal to 1 of 40 payments) and then slightly adjust to a higher future value.
The way I tried to go about this was to:
A) Solve for PMT(as opposed to PV) of the future value
B) In a second column take that 1 payment and calculate the future value of that for the remainder of the 40 years
C) In the following PMT calculation, calculate the pmt of using time value of 39 years(as opposed to 40) and subtracting the future value of the second column from the future value that needs to be solved for(i.e. 1 payment maybe grew from $8k to $40k in 40 years and so if you subtract from maybe the $830k in 40 years you end up solving what the PMT is for $790k in 39 years.
Both calculation 1 and 2 have the same future value in the 40th year. The problem is that 1 should be more exponential than 2 and 2 should be a lot flatter in the PMTs for each year. The problem is that 2 is ending up higher across the board so I know at least 1 of the methodologies I used above is flawed which is really causing me to scratch my head because they both seem like the logical way to proceed.
If anybody can identify my mistake I would really appreciate it. I'm new here so I don't know how to attach the excel spreadsheet for you guys to look at. Also if you can just tell me how to calculate the future value of uneven payments than I should at least be able to test each of them and know which one is wrong(if not both).
Thanks in advance!
