Financial Function Help

mjmoon

Board Regular
Joined
Mar 1, 2005
Messages
249
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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

nicolewh

Well-known Member
Joined
Aug 7, 2009
Messages
554
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

Board Regular
Joined
Mar 1, 2005
Messages
249
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top