Financial formulae

[an]

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)

Where:
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

 

[Ron Rosenfeld]

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)

Where:
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

 

[an]

Ok, RR.

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

Many thanks.
an

 

[Ron Rosenfeld]

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