I need to calculate the principal balance on approximately 500 individual notes at the end of each year for the next 15 years. Below is the information I have to work with for each loan along with their cell reference:
F2 = Current Principal Balance
K2 = Interest Rate (this is fixed for each loan scenario)
I2 = Payment Frequency (12=monthly, 4=quarterly, 2=semi-annual,1=annual)
G2 = Schedule payment amount
J2 = Total payments made in 12 months (formula = I2 * G2)
In columns V..AJ will be the results of the formula for the principal balance at each year end.....ie. V2 = 12-31-10, W2 = 12-31-11, X2 = 12-31-12
The start date can be assumed to be 1-1-2010 on all notes.
In summary, what I am really in need of is a formula on how to calculate either the amount of interest or the amount of principal paid in one year, given the various payment frequencies. From there I should be able to formulate what I need. I have looked at all the various financial functions but am struggling with how to get the result I am looking for.
Your time and input is appreciated. Thank you.
F2 = Current Principal Balance
K2 = Interest Rate (this is fixed for each loan scenario)
I2 = Payment Frequency (12=monthly, 4=quarterly, 2=semi-annual,1=annual)
G2 = Schedule payment amount
J2 = Total payments made in 12 months (formula = I2 * G2)
In columns V..AJ will be the results of the formula for the principal balance at each year end.....ie. V2 = 12-31-10, W2 = 12-31-11, X2 = 12-31-12
The start date can be assumed to be 1-1-2010 on all notes.
In summary, what I am really in need of is a formula on how to calculate either the amount of interest or the amount of principal paid in one year, given the various payment frequencies. From there I should be able to formulate what I need. I have looked at all the various financial functions but am struggling with how to get the result I am looking for.
Your time and input is appreciated. Thank you.