Most efficient formula for calculating principle and interest RRS feed

  • 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


All replies

  • Hi, 

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

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



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

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Wednesday, January 9, 2019 7:36 AM
  • I just used nested if statements for each
    • Marked as answer by JHarding08 Sunday, January 13, 2019 11:56 PM
    Sunday, January 13, 2019 11:56 PM