reremmak said:
I tried doing this with the FVSCHEDULE function,
but my result is way to high.
Well, it would help if you posted exactly how you are using FVSCHEDULE, the
result that you got, and the result that you expect (or thereabouts).
FVSCHEDULE worked as I expected for your problem. But that is based on my
interpretation of your problem statement. There is room for interpretation.
First, when you say "1% in the first quarter", I assume you mean that the
quarterly rate is 1%, not that the annualized rate for the quarter is 1%
and, thus, the quarterly rate is either 1%/4 or (1+1%)^(1/4)-1, again
subject to interpretation (which you would need to clarify).
Second, when you say that the rate is increased by 10% each quarter and the
rate for the second quarter is 1.1%, I assume you mean that the rates for
subsequent quarters are 1.2100%, 1.3310%, etc through 8.1403% for the last
(partial) quarter, not 1.2%, 1.3% etc through 3.2% (i.e. adding 0.1% each
quarter). Your example of only the first two quarters is not sufficient to
resolve this ambiguity dispositively.
Third, you fail to say whether or not interest is compounded; and if it is,
what the compounding frequency is. But since FVSCHEDULE assumes compounded
interest, quarterly in this case, I will make the same assumption.
Finally, note that 11/20/2009 is not an integral multiple of quarters after
4/15/2004. The last full quarter ends on 10/15/2009. For the last partial
quarter, I assume that you prorate simple interest on a daily basis. But
FVSCHEDULE will not be able to help you with that computation.
So, to be sure that we have a common understanding of the problem, as well
as to give you a way of checking your use FVSCHEDULE, let's consider the
interest accumulated through 10/15/2009, the last full quarter.
If the interest rates are in B2:B23, FVSCHEDULE(10000,B2:B23) results in
about 20,117.56.
That is the same result that I get when I set up the following model.
A1, loan origination date: 4/15/2004
C1, loan amount: 10000.00
A2, quarter end date: =EDATE($A$1,3*(ROW()-ROW($A$1)))
B2, quarterly rate: =1%*(1+10%)^(ROW()-ROW($A$2))
C2, quarter end balance: =C1*(1+B2)
Format A2 as Date; format B2 as Percentage with 4 or more decimal places;
and format C1 and C2 as Number with 2 decimal places.
Copy A2:C2 down through A23:C23.
The result in C23 is about 20,117.56, the same as what FVSCHEDULE returns.
If you expect something else, the model might reveal some "mistakes" in
interpretation -- points that you need to clarify.
Finally, the following is one way to prorate the interest through the final
partial quarter. This also demonstrates one way to enter FVSCHEDULE as an
array formula without having to enumerate all of the quarterly interest
rates in B2:B23.
In C24:
=FVSCHEDULE(10000,1%*(1+10%)^(ROW(1:22)-1))
* (1+(DATE(2009,11,20)-EDATE(DATE(2004,4,15),3*22))
* 1%*(1+10%)^22*4/365)
Note that as an array formula, you commit using ctrl+shift+Enter, not just
Enter. If you make a mistake, press F2, then press ctrl+shift+Enter. You
should see curly braces around the entire formula, i.e.
{=FVSCHEDULE(...)*...}.
The result is about 20,763.64.
Of course, that is the total balance of the loan. The amount of interest is
that amount minus the loan principal; namely:
=C24-C1
By the way, instead of hardcoding 22, the number of full quarters, you could
compute it (in D1, for example), namely:
=INT(DATEDIF(DATE(2004,4,15), DATE(2009,10,15), "m") / 3)
Then the array formula in C24 becomes (remember to commit with
ctrl+shift+Enter):
=FVSCHEDULE(10000,1%*(1+10%)^(ROW(INDIRECT("$1:$"&D1)-1))
* (1+(DATE(2009,11,20)-EDATE(DATE(2004,4,15),3*D1))
* 1%*(1+10%)^D1*4/365)
Please let me know if that works for you.
If not what clarifications of your problem description are needed to match
your expectations?
----- original message -----
