Compounding Interest - Create a running balance sheet

Howdy folks!

I hope you're all doing well. I've searched on the interwebs and on here but have yet to find the answer we need. Here is my scenario (and i'll include sample data) too: Disneyland has a loan via a developer (Bob) that is compounded quarterly. There is NO payment schedule. NO minimum payments. Disneyland just makes payments when they can. Of course, if Disneyland doesn't keep up with the interest, they're going to be paying tons more than they should.

What we want to know is how to create a running balance sheet for Disneyland. In other words, if Disneyland calls us up and says "Hey, how much do we owe on this 40-year compound interest loan TODAY?" We want to be able to give them an accurate balance for that quarter.

This isn't an interest-bearing savings account where the compounded interest is good; it's a loan where the compounded interest is going to hurt Disneyland if they fail to keep up with the accruing interest.

As you can see from the attached image, Disneyland made *NO* payments for at least the first four quarters after the loan was issued. So, the interest kept piling up and the outstanding amount keeps increasing.

While not shown, we did get a few payments from them in later years (2016 and on). All very random amounts. We want to make sure that we're accounting for the compounded interest correctly after inserting the payments into the running balance sheet.

What we want to do is get an accurate balance sheet (similar to using an old-school checkbook and manually keeping tabs on your balance). We want it to show us the current balance if they called us and asked. Again, the Principal Amount Paid column (shown in attached graphic) will reflect WHEN they make payments but there is *NO* minimum payment due like a credit card or vehicle loan.

Starting balance / original loan amount	Issuance Date	Interest Rate...	Compounded...	Maturity Date
$630,000.00	2/11/2013 (first portion was pro-rated interest due $13,650) see attached graphic	10%	Quarterly. After 1st pro-rated interest calculation, quarters start on 5/1/2013. So, next quarters would be 8/1/2013, 11/1/2013, 2/1/2014, and so on.	6/30/2053

Hopefully my scenario makes sense (names were changed to protect the innocent). Holler if you need more details. THANKS for looking and trying.

Attaching a sample spreadsheet for your review.

Color-coded the information on there. Legend is here as well as on Sheet1.

• RED = Unknown formulas. We are not sure if this is calculating the Balance correctly
• GREEN = KNOWN values provided from the original loan paperwork (see the attached graphic)
• YELLOW = KNOWN values based on payments that we know were provided ($ and date).
• GREY TEXT & ORANGE $ = We are not sure what to use for a correct Interest Due calculation (likely based on the running balance)
• PEACH FILL = columns/values that are likely less important to this request. We had them on there for curiosity.