Results 1 to 2 of 2

Thread: Weighted Average Loan Payment Formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Location
    Portland, Oregon
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Weighted Average Loan Payment Formula

    Hi everyone,

    I'm attempting to create a formula that calculates a "paid-to date" for a loan that has multiple principal balances. I want to be able to plug in a payment amount and have a formula in the Interest Paid-to column determine the paid-to date by applying the payment to the loan with the oldest closing date until it is caught up to the next one.

    For example, if a payment of $5,200 was made in October, the paid-to dates would move forward as follows:

    111223a: 10/02/19 ($5,166.67/$166.67 = 31 days)
    111223b: 10/02/19 ($33.33/$33.33 = 1 day)
    111223c: 11/01/19
    Loan Number Subject Address Loan Amount Interest Rate Funding Date Closing Date Interest Paid-to Daily Interest Rate
    111223a 123 ABC Street 500,000.00 12.00% 09/01/19 09/01/19 10/02/19 166.67
    111223b 123 ABC Street 100,000.00 12.00% 09/25/19 10/01/19 10/02/19 33.33
    111223c 123 ABC Street 300,000.00 12.00% 10/25/19 11/01/19 11/01/19 100.00

    My initial thought was to figure out a weighted average system to apply the daily interest rate weighted by the closing date using SUMPRODUCT, however I can't seem to work anything out. Ultimately I need the formula to turn the payment into a number of days based on the daily interest rate, and keep the paid-to dates even for all three principal balances once it passes the closing date for each.

    Any input would be appreciated, thank you!

  2. #2
    New Member
    Join Date
    Aug 2019
    Location
    Portland, Oregon
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Weighted Average Loan Payment Formula

    If there's anything I can do to help clarify feel free to ask.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •