JerryKlmns
IS-IT--Management
The function needed is to calculate the equal monthly installment of an amortized loan using actual days of month and 365 days in year! Payment is due on the end of each period.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Interest Calulation Function
- Thread starter JerryKlmns
- Start date
- Status
The function needed is to calculate the equal monthly installment of an amortized loan using actual days of month and 365 days in year! Payment is due on the end of each period.
JerryKlmns,
I have never seen all of what you are looking for in a single excel function.
I do have an amortization schedule excel wksht that I would share with you but that presents a dilema. I do have a gmail account.
rvnguy
"I know everything..I just can't remember it all
I have never seen all of what you are looking for in a single excel function.
I do have an amortization schedule excel wksht that I would share with you but that presents a dilema. I do have a gmail account.
rvnguy
"I know everything..I just can't remember it all
- Thread starter
- #3
JerryKlmns
IS-IT--Management
rvnguy,
I end up using
Interest(i)=RemainingCapital(i)*IntRate*ActualMonthDays(i)/365
CapitalPaid(i)=Installment(i)-Interest(i)
RemainingCapital(i+1)=RemainingCapital(i)-CapitalPaid(i)
Installment(i+1)=Installment(i)
RemainingCapital(0)=Loan Capital
and goal seek to set Sum(CapitalPaid(i))= Loan Capital by changing cell of Installment(i) and adjusting last installment to deal with rounding...
Schedule dates are taking care of this (29/Feb/200x),fine
Are there any thoughts or suggestions you could share?
Or have you a different way ??
---------------------------------------------------
Posting an e-mail account is not recommended by this site.
I end up using
Interest(i)=RemainingCapital(i)*IntRate*ActualMonthDays(i)/365
CapitalPaid(i)=Installment(i)-Interest(i)
RemainingCapital(i+1)=RemainingCapital(i)-CapitalPaid(i)
Installment(i+1)=Installment(i)
RemainingCapital(0)=Loan Capital
and goal seek to set Sum(CapitalPaid(i))= Loan Capital by changing cell of Installment(i) and adjusting last installment to deal with rounding...
Schedule dates are taking care of this (29/Feb/200x),fine
Are there any thoughts or suggestions you could share?
Or have you a different way ??
---------------------------------------------------
Posting an e-mail account is not recommended by this site.
- Status