Syntax. FVSCHEDULE(Capital,Interest)
Definition. This function calculates the final value for capital with variable period interest rates.
Arguments
Principal (required) The capital that will receive interest payments.
Schedule (required) A list of the interest rates for the periods without gaps, which follows the required order. This argument can be a cell reference or a matrix (values inside of braces, separated by commas). Values that cannot be evaluated as numbers cause the error
#VALUE!. Empty cells are handled as if they had the number zero.
Background. This calculation takes into consideration the fact that the interest is added to the capital at the end of the period:
Period end value = (Period start value) · (1 + Period interest rate)
Of course, this includes compound interest.
However, the result cannot be displayed in clear formulas. A concession must be made to evaluate individual periods more precisely than with a constant interest rate over the entire time.
Example. German Type B federal savings bonds cannot be dealt at the stock exchange. They are securities whose interest is calculated based on annual fixed interest rates. The interest is added to the balance at the end of the interest period and is then paid interest in the remaining periods at the interest rate that is in effect at that time. This is how they differ from type A federal savings bonds, whose interest is paid and not normally reinvested at the same terms.
On August 12, 2008, the German Federal Bank published the data shown in Table 15-3.
Table 15-3. Conditions for Federal Savings Bonds
Duration Year | Nominal Interest |
|---|---|
2010/2011 | 0.25% |
2011/2012 | 0.50% |
2012/2013 | 1.00% |
2013/2014 | 1.75% |
2014/2015 | 2.50% |
2015/2016 | 2.75% |
2016/2017 | 2.75% |
The yields can be recalculated on a worksheet by using FVSCHEDULE() and RATE(), as shown in Figure 15-4.
The function FVSCHEDULE() determines the status in the individual years by using the interest rates in column C from the beginning to the respective year. RATE() then determines the yield from this value and from the purchase price of $100.00.
This can be interpreted as follows: If the amount was invested at a regular constant interest rate (the same yield), the same result would be reached as with changing interest rates.