Financial formulae
 

Hello!

I have:
A1 Value of loan
B1 annual interest
C1 60 Months (Period)
D1 Loan amortization (to intercalate)
E1 (Formulae to calc montly income of interest)
F1 (Formulae to calc montly income of loan)
G1 =Sum(E1+F1)

Whe
1st income = 100% interest and 0% amortization;
Last income = 0% interest and 100% amortization.

Is it possible with excel formulae, please?

Thanks in advance.
an

On Thu, 10 Feb 2005 04:43:04 -0800, "an"
wrote:

Hello!

I have:
A1 Value of loan
B1 annual interest
C1 60 Months (Period)
D1 Loan amortization (to intercalate)
E1 (Formulae to calc montly income of interest)
F1 (Formulae to calc montly income of loan)
G1 =Sum(E1+F1)

Whe
1st income = 100% interest and 0% amortization;
Last income = 0% interest and 100% amortization.

Is it possible with excel formulae, please?

Thanks in advance.
an

You need to be more clear.

1. Amortization usually means the total payment. But I don't know what
"intercalate" (D1) means in this scenario, as it would seem to be identical to
G1.

2. Since you are writing about income from the loan, I assume you are making
the loan. Is that the case?

3. Although it is easy to calculate the first payment as "interest only"
(=Value * annual_interest/12), the usual subsequent procedure, in the US, would
be to then calculate a regular monthly payment (using the PMT function in
Excel), which would pay off the loan after another 59 (or 60) payments. If
there were a residual, it could be added to the last month's payment.

If those assumptions of mine are correct, then the formulas are as follows:

A1: Since this is money you are lending, enter it as a negative value
D1: =G1
E1: =-$B$1/12*A1
F1: 0
G1: =E1+F1

A2: =A1+F1
D2: =G2
E2: =IPMT($B$1/12,ROW()-1,$C$1-1,$A$1)
F2: =PPMT($B$1/12,ROW()-1,$C$1-1,$A$1)
G2: =E2+F2

Select A2:G2 and fill down to row 60.

Note that if you change the rows, you will need to adjust the function ROW()-1
to reflect the correct payment number.

Depending on your initial values, there may be a few cents balance remaining
after the final payment. That could be your final "income".


--ron

Ok, RR.

Thanks for your reply.
In fact, your help is the solution for my question.

Many thanks.
an

-----Original Message-----
On Thu, 10 Feb 2005 04:43:04 -0800, "an"

wrote:

Hello!

I have:
A1 Value of loan
B1 annual interest
C1 60 Months (Period)
D1 Loan amortization (to intercalate)
E1 (Formulae to calc montly income of interest)
F1 (Formulae to calc montly income of loan)
G1 =Sum(E1+F1)

Whe
1st income = 100% interest and 0% amortization;
Last income = 0% interest and 100% amortization.

Is it possible with excel formulae, please?

Thanks in advance.
an

You need to be more clear.

1. Amortization usually means the total payment. But I
don't know what
"intercalate" (D1) means in this scenario, as it would
seem to be identical to
G1.

2. Since you are writing about income from the loan, I
assume you are making
the loan. Is that the case?

3. Although it is easy to calculate the first payment
as "interest only"
(=Value * annual_interest/12), the usual subsequent
procedure, in the US, would
be to then calculate a regular monthly payment (using the
PMT function in
Excel), which would pay off the loan after another 59 (or
60) payments. If
there were a residual, it could be added to the last
month's payment.

If those assumptions of mine are correct, then the
formulas are as follows:

A1: Since this is money you are lending, enter it as a
negative value
D1: =G1
E1: =-$B$1/12*A1
F1: 0
G1: =E1+F1

A2: =A1+F1
D2: =G2
E2: =IPMT($B$1/12,ROW()-1,$C$1-1,$A$1)
F2: =PPMT($B$1/12,ROW()-1,$C$1-1,$A$1)
G2: =E2+F2

Select A2:G2 and fill down to row 60.

Note that if you change the rows, you will need to adjust
the function ROW()-1
to reflect the correct payment number.

Depending on your initial values, there may be a few
cents balance remaining
after the final payment. That could be your
final "income".


--ron
.

On Thu, 10 Feb 2005 07:24:59 -0800, "an"
wrote:

Ok, RR.

Thanks for your reply.
In fact, your help is the solution for my question.

Many thanks.
an

Glad to hear that. Thank you for the feedback.
--ron