## Example of using the Future Value Formula on a Commercial Loan

My first post on this blog was in August of 2014 and it was called: No XNFV Excel Function? The idea was a formula for calculating future value with dates. There was a XNPV function for present value, but no function for future value. My formula looks like this:

=SUMPRODUCT((1+APY)^((MAX(Dates)-Dates)/365)*Data)

Of course there are other workarounds, such as:

=FV(APY,(MAX(Dates)-MIN(Dates))/365,,-XNPV(APY,Data,Dates))

Fictitious Example: Your firm, First National Bank, is the lead underwriter and servicer of a \$50 million commercial loan. The property is a large apartment complex. The loan is participated out to four other commercial lenders. The combined group of lenders is called The 325 Commercial Development Group. Soon after the loan settled, the borrower stopped making payments. It was learned that the property was run down and the local municipal public works department was threatening to condemn the property. Obviously, there was deception on the part of various persons, to conceal the actual condition of the property. Since the participation agreement stated that all participants were obligated to do their own due diligence on the condition of the property, all five members of the 325 Commercial Development Group are responsible for their portion of the loan.

The 325 Commercial Development Group takes position of the property and sues the commercial broker, property manager, the inspection firm and various other players for a fixed amount. As the servicer for the loan, your firm pays all expenses and collects reimbursements on the property while the lawsuit proceeds. These net expenses will be applied on a pro rata basis to members of the group at the end of the lawsuit.

The 325 Commercial Development Group wins the lawsuit for a set amount. Your firm has paid out a large amount for expenses over the three years to clean up and repair the property . Your job is to calculate how much interest has accumulated on these net expenses, and allocate the interest and expenses to all members of the group. The final distribution of proceeds from the lawsuit including net expenses, and proceeds from the sale of the property, is made on 2/15/19.

There are three years of expense and credits, the first dozen or so look like this:

The interest rate is entered on the data sheet:

Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.