Financial Function Help

[mjmoon]

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.

 

[nicolewh]

FV Function is the ending balance after a certain number of payment periods. this would perhaps be the best function to use. However if you need other info IMPT seems to be what you would need if you need to just total the interest. I would also check out PMT - this is the actual payments of each loan(but this may require a lot of unnecessary spreadsheet usage.).

Anyway Goodluck!

Nicole

 

[mjmoon]

Thanks for your help. Unfortunately I couldn't use either one of these functions to get the answer what I was looking for. The issues I had with FV function was that it compounds the interest monthly and in my scenario the interest was getting paid monthly thus creating a higher end result. With the IPMT function, it does not take into account the amount of the principal payment that occurs monthly as it only calculates the interest paid from start period to end period and assumes a constant princpal balance.

Based on my research I have resolved to the fact that it can't be calucated easily based on my given scenarios, which I am having a hard time admitting since I have always told my co-workers "Excel can do anything".

To resolve my problem I just assumed all payments were made annually and did not take into account semi-annual, quarterly or monthly payments. It got me close and it will error on the high side which was also acceptable.

Thanks again for you help.