Compounding Interest - running balance sheet

abarbee314

New Member
Joined
Apr 5, 2013
Messages
16
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 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. Hopefully my scenario makes sense (names were changed to protect the innocent). Holler if you need more details. THANKS for looking and trying. :)

Starting balance / original loan amountIssuance DateInterest Rate...Compounded...Maturity Date
$630,000.002/11/2013 (first portion was pro-rated interest due $13,650) see attached graphic10%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

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

But we did get a few payments from them in later years. 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 that would show us the current balance if they called us and asked.
Disneyland - compound balance sheet entries.jpg
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Watch MrExcel Video

Forum statistics

Threads
1,129,567
Messages
5,637,091
Members
416,957
Latest member
Brovashift

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