# Most efficient formula for calculating principle and interest

• ### Question

• I have a spreadsheet that calculates loan payments based off of loan amount, payments and monthly rate

Columns are:

 Payment # Payment Date Beginning Balance Interest Paid Principal Payment Ending Balance Total Interest Paid Total Principle Paid Total P & I Paid

Each row is a payment date and takes beginning balance and subtracts principal payment. IT also calculates running toal of total interest apid, total principle paid and total principle and interest by referencing previous payment date

I have another sheet that calculates total principle and interest paid per year, by taking the year total P&I and subtracting from previous year P&I (for example total P*I for year 16 is cell I202, total for that year is I202-I190 (Year15 total)

When default terms are applied, i can get yearly differences all the way until year 30 since that was the original term (360 pay periods).

I just implemented an accelerated payment feature where extra money is added to the principle payment to pay off the loan faster.  After the loan is paid, the columns are all 0

Where I run into issues is when the balance goes to 0 mid year. So my calculation for year 16 -year 15 would be 0 for year 16, giving me a negative value. I need to be able to test if the year end value is 0 and if so, move back payment dates until the P&I for a date in that year is not 0

I could do IF(I202>0, I202-I190, if(I201>0,I201-I190, if I200>0,I200-I190,...etc

This would be alot of manual entry and processing, could I use offset to make the formula smaller or another way to do it?

Tuesday, January 8, 2019 9:04 PM

• I just used nested if statements for each
• Marked as answer by Sunday, January 13, 2019 11:56 PM
Sunday, January 13, 2019 11:56 PM

### All replies

• Hi,

If possible, you could send this file to our email address:

ibsofc@microsoft.com

Note: Please add the URL of the case in the email subject or body, please make sure that you have hidden your private information.

Regards,

Emi

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact: tnsf@microsoft.com.