How to Calculate a Preferred Return in Excel, Step by Step

To calculate a preferred return in Excel, build one row per period with the beginning unreturned capital, the unpaid pref carried in, the period's accrual (capital x rate for a simple pref, (capital + unpaid pref) x rate for a compounding one), the cash paid, and the ending balances. Cash pays the accrued pref first, and anything left over reduces capital if the agreement says so. Every later row starts from the balances the row above it ends with.

This guide is for sponsors who want a schedule that matches their operating agreement, and for investors checking one. The concepts (cumulative, simple, compounding) are explained in what a preferred return is; here we build the sheet. The formulas work the same in Excel and Google Sheets.

What inputs does a preferred return schedule need?

Put the assumptions at the top of the sheet so every formula points to them:

Then read the operating agreement for four answers, because the sheet must follow it, not the other way around:

1. Cumulative or not? If not cumulative, unpaid pref is dropped each year and column D is always zero.
2. Simple or compounding? Compounding means pref accrues on unpaid pref too.
3. On what base? Original capital, or unreturned capital? Most agreements use unreturned capital, so returned capital stops accruing.
4. What does cash pay first? Usually accrued pref, then capital. Some agreements send operating cash only to pref and route the excess to the split.

How do you lay out the columns?

Starting in row 5, one row per year (or per quarter, with the rate divided by 4):

The $B$3 switch makes one sheet do both versions: with 0, column E is C5*rate (simple); with 1, it is (C5+D5)*rate (compounding). Column I sends cash beyond the pref to capital until capital is fully returned, and column J catches anything beyond that.

A check row at the bottom keeps you honest: total cash in column B must equal total pref paid (G) plus capital returned (C5 minus the last I) plus the excess (J).

Worked example: a four-year schedule with a refinance

All numbers are hypothetical, for illustration only. They are not a projection, and every investment can lose money.

Investors contribute $500,000 at an 8% cumulative pref on unreturned capital. Distributions are $25,000 in year 1, $35,000 in year 2 and $40,000 in year 3. At the end of year 3, a refinance returns $160,000 more, so year 3's cash is $200,000. Year 4 pays $30,000.

Simple (cumulative, not compounded):

Compounding (annually):

What the tables show:

• In year 2 the compounding version accrues $41,200: 8% of $515,000 ($500,000 plus $15,000 unpaid). The simple version accrues $40,000.
• The refinance clears the unpaid pref first ($60,000 simple, $62,896 compounding) and returns the rest as capital.
• After the refinance, accrual drops to $28,800 because the base is now $360,000. If your agreement accrues on original capital instead, year 4 would stay at $40,000, a big difference, so check which one you have.
• Over four years, compounding moves $3,127.68 more of the same cash to pref ($151,927.68 against $148,800), so $3,127.68 more capital is still unreturned. Small here, because the shortfall was short; it grows with longer shortfalls.

Check year 3, simple: $60,000 + $140,000 = $200,000 of cash. Over four years: $25,000 + $35,000 + $200,000 + $30,000 = $290,000 of cash = $148,800 of pref paid + $141,200 of capital returned ($500,000 - $358,800).

How do you handle partial periods and mid-year capital?

Real deals do not close on January 1. If the agreement accrues daily:

• First period: =C5*$B$1*days/365. A $500,000 deal that closes July 1 accrues $500,000 x 8% x 184 / 365 = $20,164.38 through December 31.
• Capital returned mid-period: split the period at the return date. Accrue on the old balance up to that date and on the new balance after.
• Quarterly distributions: use quarters as rows and $B$1/4 as the rate, or keep annual rows and treat the quarterly cash as one annual total, which is only exact for a simple pref with no mid-year capital changes.

If you need investor IRR alongside the schedule, put the dated cash flows in two columns and use =XIRR(values, dates). IRR is a different measure from the pref, as our post on preferred return vs. hurdle rate shows.

What mistakes break a pref spreadsheet?

• Accruing on original capital when the agreement says unreturned capital (or the reverse).
• Compounding by accident. A formula that multiplies (C5+D5) when the agreement says simple overstates what is owed.
• Netting the sponsor's co-investment wrong. If the sponsor's own money sits in the investor class, it accrues pref like everyone else's.
• Hard-coding numbers. Every accrual should point to the rate cell, so a change in one place flows through.
• Ignoring the order at a sale. If the agreement returns capital before unpaid pref, the sale rows must too. For the full sale waterfall, see how a waterfall and promote work.

When the schedule is ready, test it against a weak year, not just the pitch case. A pref the deal cannot pay in a slow year is a promise investors will remember; multifamily underwriting basics covers the downside cases.

How DoorRaise fits in

DoorRaise's standard operating agreement uses a cumulative, non-compounded pref that accrues daily from the closing date on each investor's unreturned capital, less operating distributions already paid. At a sale or refinance, capital comes back first, then any unpaid pref, then the sponsor's split. DoorRaise also forms the deal's LLC, files Form D and state notices, verifies accredited investors, and has investors fund by wire into escrow at Enterprise Bank & Trust, an independent bank; distributions are quarterly by default. Fees are $1,500 to set up (non-refundable), $5,000 at closing (only if the deal closes) and $3,500 a year.

To check a sale against your terms without a spreadsheet, try the profit split calculator, or create your sponsor page when you are ready.

FAQ

What is the preferred return formula in Excel?

For a simple pref: unreturned capital x rate, for example =C5*$B$1. For a compounding pref: (unreturned capital + unpaid pref) x rate, =(C5+D5)*$B$1. Pay it with =MIN(cash, unpaid + accrued).

How do you calculate accrued preferred return?

Add each period's accrual to the unpaid balance carried in, then subtract what was paid. The ending unpaid balance is the accrued pref still owed, and it carries into the next period if the pref is cumulative.

How do you calculate a preferred return compounded annually?

Each year, apply the rate to unreturned capital plus the unpaid pref from the year before. In the example, year 2 accrues 8% of $515,000, or $41,200, instead of $40,000.

What is the compounding preferred return formula?

Accrued pref for a period = (unreturned capital + unpaid pref at the start) x rate for the period. For a whole hold with no payments, the balance owed is capital x ((1 + rate) ^ years - 1).

Does a refinance change the preferred return?

If the pref accrues on unreturned capital and the refinance returns capital, yes: every later accrual is smaller. In the example, accrual fell from $40,000 to $28,800 a year.

Sources

• IRS: Instructions for Schedule K-1 (Form 1065)
• IRS: Instructions for Form 1065

The example is a simplified illustration with made-up numbers, not a projection or an offer. This article is general information as of October 2026, not legal, tax or investment advice. Your operating agreement controls how the pref is calculated; have your own advisers check your model. DoorRaise is not a law firm, broker-dealer, investment adviser or funding portal.