Accrued unpaid preferred interest

**bdwttu** · 08-06-2012, 12:32 PM

I can model this out in several columns but is there a way to calculate accrued unpaid preferred interest by inputting the number of periods, rate and initial basis?

For example, an equity investor invests $1M and is due a 10% preferred annual return. If the cash flows from the investment are insufficient to pay the pref, the investors equity basis increases by the amt due. So if, for example, the received no cash flow his basis would be:

Year 1: $1,000,000
Year 2: $1,000,000 x 1.1 = $1,100,000
Year 3: $1,100,000 x 1.1 = $1,210,000
etc...

Thanks

**shg** · 08-06-2012, 12:41 PM

It's simple enough to do, but the details depend entirely on the layout. Post a workbook.

**bdwttu** · 08-06-2012, 12:51 PM

accrued return worksheet.xlsx

Here is an example. As you can see it is currently calculating it wrong - not paying interest on the adjusted basis from the unpaid returns. Thanks for your help.

**shg** · 08-06-2012, 01:25 PM

You need to show month-by-month interest payments, do you not, since it is only the unpaid interest that is converted to equity? If none is paid, then isn't it just ..

=FV(D5/12, D6, 0, -D2) - D2 = $6,453,089

**bdwttu** · 08-06-2012, 02:02 PM

You're right. I was making it more difficult than it was. Thanks for your help.

**Cutter** · 08-06-2012, 04:20 PM

@ bdwttu

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.