I need to calculate the amount of interest due on a 4/15/04 loan and will be
repaid 11/20/09. Assume the principal is $10,000 and the interest rate has
been changing quarterly. Further assume the interest rate was 1% in the
first quarter and has been increasing by 10% every successive quarter (thus
in the second quarter the total interest was 1.1%). I tried doing this with
the FVSCHEDULE function, but my result is way to high.

Thanks

"reremmak" wrote:
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 -----

"reremmak" wrote in message
...
I need to calculate the amount of interest due on a 4/15/04 loan and will
be
repaid 11/20/09. Assume the principal is $10,000 and the interest rate
has
been changing quarterly. Further assume the interest rate was 1% in the
first quarter and has been increasing by 10% every successive quarter
(thus
in the second quarter the total interest was 1.1%). I tried doing this
with
the FVSCHEDULE function, but my result is way to high.

Thanks

PS....

I wrote:
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.

As a double-check, the following is how to prorate the interest (actually
the FV) in the model that I described.

A24, loan termination date: 11/20/2009
B24, quarterly rate for last partial quarter: copy B23
C24, loan balance on termination date: =C23*(1+(A24-A23)*B24*4/365)


----- original message -----

"Joe User" <joeu2004 wrote in message
...
"reremmak" wrote:
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 -----

"reremmak" wrote in message
...
I need to calculate the amount of interest due on a 4/15/04 loan and will
be
repaid 11/20/09. Assume the principal is $10,000 and the interest rate
has
been changing quarterly. Further assume the interest rate was 1% in the
first quarter and has been increasing by 10% every successive quarter
(thus
in the second quarter the total interest was 1.1%). I tried doing this
with
the FVSCHEDULE function, but my result is way to high.

Thanks

An alternative is to do the calculations yourself, as in:

A2: 4/15/04
B2: 1%
C2: 0
D2: 10000
A3: =date(year(a2),month(a2)+3,day(a2))
B3: =B2*1.1
C3: =D2*B2
D3: =D2+C3
Copy down until you reach 10/15/09
Add interest for the period 10/15/09 to 11/20/09

Regards,
Fred.

"reremmak" wrote in message
...
I need to calculate the amount of interest due on a 4/15/04 loan and will
be
repaid 11/20/09. Assume the principal is $10,000 and the interest rate
has
been changing quarterly. Further assume the interest rate was 1% in the
first quarter and has been increasing by 10% every successive quarter
(thus
in the second quarter the total interest was 1.1%). I tried doing this
with
the FVSCHEDULE function, but my result is way to high.

Thanks