I need to calculate the interest charged each month for a loan/lease with payments in advance/arrears and with a regular payment stream or with a different 1st payment, all on the same presentation...
C8: Total amount of loan (100,000)
C9: 1st payment amount (10,000)
C10: FV (0)
C11: Total term period (60)
C12: # of payments in year (12)
C13: number of payments total (60)
C14: Rate (8.00)
B35: month number (1)
B36: month number (2) etc...
D4: (advance/arrears indicator) (1)
With if/then formulas, I got it all to work except for when a 1st payment is different than the stream of payments and the loan/lease is in advance
The problem is as follow:
At month 1:
I need to calculate the interest charged during the 1st month with IPMT(C14/C12,1,1,-C8,(C8-C9),D4)= $666,67 (fine there)
At Month 2:
I need to calculate the payment for the rest of the stream with regular payments) in arrears with
PMT(C14/C12,(C13-1),-FV(C14/C12,1,C9,-C8,D4),C10,D4)) which is $1,863,76 (fine there too)
Now to calculate the interest charged in month 2:
IPMT($C$14/$C$12,(B36-1),($C$11-1),-FV($C$14/$C$12,1,$C$9,-$C$8,$D$4),$C$10,$D$4) The result comes up at $0 cause it's in advance and the formulas think it's the 1st month of a new payment stream which in effect it's the 2nd month of my payment stream after the 1st payment..
Is there a formula that i can use that would solve that or am I seeing the leaves without seeing the forest?
Not explained right uh??? Sorry...
C8: Total amount of loan (100,000)
C9: 1st payment amount (10,000)
C10: FV (0)
C11: Total term period (60)
C12: # of payments in year (12)
C13: number of payments total (60)
C14: Rate (8.00)
B35: month number (1)
B36: month number (2) etc...
D4: (advance/arrears indicator) (1)
With if/then formulas, I got it all to work except for when a 1st payment is different than the stream of payments and the loan/lease is in advance
The problem is as follow:
At month 1:
I need to calculate the interest charged during the 1st month with IPMT(C14/C12,1,1,-C8,(C8-C9),D4)= $666,67 (fine there)
At Month 2:
I need to calculate the payment for the rest of the stream with regular payments) in arrears with
PMT(C14/C12,(C13-1),-FV(C14/C12,1,C9,-C8,D4),C10,D4)) which is $1,863,76 (fine there too)
Now to calculate the interest charged in month 2:
IPMT($C$14/$C$12,(B36-1),($C$11-1),-FV($C$14/$C$12,1,$C$9,-$C$8,$D$4),$C$10,$D$4) The result comes up at $0 cause it's in advance and the formulas think it's the 1st month of a new payment stream which in effect it's the 2nd month of my payment stream after the 1st payment..
Is there a formula that i can use that would solve that or am I seeing the leaves without seeing the forest?
Not explained right uh??? Sorry...
