here's my calculations.
column H shows the end of the semi-annual time period.
the first year ends at time period 2.
the last year ends at time period 16.
column I shows the cash flow.
20,000 is required at the end of time period 4 (end of 2 years).
40,000 is required at the end of time period 8 (end of 4 years).
80,000 is required at the end of time period 16 (end of 8 years).
column J shows the present value of these required maintenance amounts dicounted at 3% per year to time period 0.
for example, 20,000 at the end of time period 4 is divided by 1.03^4 to get the present value of 17,769.74 discounted to time period 0.
cell I20 shows the sum of the payments required.
cell J20 shows the sum of the present value of the payments required.
cell A4 shows the euivalent semi-annual payment required to satisfy those payments over the 8 year period.
that amount is $7897.35 rounded to the nearest penny.
column K shows the equivalent cash flow using those semi-annual payments.
column L shows the equivalent present value of the cash flow using those semi-annual payments.
cell K20 shows the sum of the equivalent cash flows using the semi-annual payments.
cell L20 shows the sum of the present value of the equivalent cash flows using the semi-annual payments.
the sum of the equivalent cash flows is not the same as the sum of the original cash flows, but .....
the sum of the equivalent present value of cash flows is the same as the sum of the original present value of cash flows !!!!!.
this is as it should be because the present value of the payment needs to be equal to the present value of the cash flows that it was created from.
column M shows the remaining balance in the payment account.
this account is set up to handle the payments required.
a positive amount in this account shows the remaining balance that is owed at the end of the time period shown.
a negative amount in this account shows the remaining balance of money that has been paid into the account.
column M account starts in time point 0 with a remaining balance of 0.
in time point 1, the first payment has been made but no money is still owed, therefore the account balance is negative.
this continues until time point 4, where the ramining balance is still negative, but less so 20,000 required payment has been added to the account, but the sum of the payments to that time are still greater than the amount owed.
this continues until the end of the 8 year time period when the remaining balance is 0.
the remaining balance is 0 because all the money that has been owed (20,000 at the end of the second year, 40,000 at the end of the fourth year, and 80,000 at the end of the 8th year) has been paid off.
a sample of the semi-period to semi-period calculations in column M is shown below:
the remaining balance at the end of time period 7 is -38,658.63.
this is multiplied by 1.03 and then the required payment of 40,000 in time period 8 is added to it and then the semi-annual payment of 7897.35 is subtracted from it to get a remaining balance of -7715.75 in time period 8.
in terms of cell address, the calculations are as follows:
the value in cell M10 is equal to the value in cell M9 * 1.03 plus the value in cell I10 minus the value in cell K10.
this becomes -38,658.63 * 1.03 + 40,000 - 7897.35 = -7715.75.
if you do the calculation yourself, you'll get -7715.7389.
the difference is because the manual calculations are using rounded numbers while the excel calculations are using the original numbers that are not rounded.
the calculation of the actual payment itself was done using the PMT function in excel.
the inputs to that function were:
=-PMT(0.03,16,J20)
.03 was the interest rater per time period.
16 was the number of time periods.
J20 was the cell that contained the present value of the cash flow which was equal to $99,199.47.
the PMT function returned a payment of -$7,897.35 which was then made positive by the minus sign in front of the PMT function.
in this function, payments are assumed to be made at the end of the time period unless otherwise specified, which was not.
the future value is also assumed to be 0 unless otherwise specified, which was not.
your solution is that the uniform semi-annual amount that could it set aside over the next eight years, at the end of each period, to meet these requirements to maintenance cost, if all the funds would earn interest at the rate of 6% compounded semi-annually, is equal to $7,897.35.