# Prepaid Expense Schedule

#### mijamo

##### New Member
Hello, I am trying to create a prepaid expense schedule to reconcile with out register monthly.

This prepaid schedule should reconcile both expenses that are recognized monthly over a certain number of periods, or expenses that are recognized on a certain date. I can't seem to get my IF formula to work.

Basically, If the expense is recognized per period, "Y" or "N", I know how to have it recognize per period... But when I have expenses that are not recognized over periods, but the entire amount is recognized at a later date, the I don't know how to get the formula to put the balance in the sheet, but show it being recognized at that expense date.

 Vendor Invoice Date Invoice Amount Recognized Per Period Expense Date Periods Balance Deferred Salesforce.com 1/31/2015 \$ 11,718.75 Y 5 \$ - Zuora 2/1/2015 \$ 32,076.00 Y 11 \$ 20,412.00 Xactly 3/7/2015 \$ 2,506.90 Y 10 \$ 1,754.83 G2E 3/6/2015 \$ 6,811.70 N 9/28/2015 1 \$ -

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Here is the formula I have for the expenses recognized per period... I just recently added the Y and N fields with the expense date...

=IF(\$B5<=I\$1,IF(SUM(\$H5:H5)>=\$C5,"",\$C5/\$F5),"")

So, basically, if it is recognized over the period, then Y, otherwise it would use the expense date, rather than the Invoice date to insert the amount in the period in which it is recognized. But the invoice amount increases the register amount for the month, then decreases in the period of the expense date.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Replies
0
Views
90
Replies
0
Views
786
Replies
3
Views
4K
Replies
1
Views
262
Replies
4
Views
231

1,195,696
Messages
6,011,178
Members
441,592
Latest member
Vasant bangalore

### 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.

### Which adblocker are you using?

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

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