Income and work

Pension, rent and interest: a three-source retirement income sheet

A retirement income sheet for pension, rent and interest: expected and received dates, a combined monthly line against bills, and working formulas.

Editorial illustration for Pension, rent and interest: a three-source retirement income sheet

A retirement income sheet with three sources needs one row per credit per month, with the expected date, the received date and the amounts. Below the entries sit two totals: income received this month and bills due this month. The difference is your margin, and the dates show you which source usually arrives late.

How should the entry tab be laid out?

ColumnHeaderContent
AMonthFirst day of the month as a real date
BSourcePension, Rent or Interest
CExpected dateThe date you normally look for the credit
DExpected amountWhat you expect in rupees
EReceived dateBlank until it lands
FReceived amountBlank until it lands
GDays lateFormula showing the delay

Add three rows each month, one for each source. In G2 use =IF(E2="","",E2-C2) and copy it down; format the column as a number, not a date. A positive number means the credit came after the expected date.

How do I total a month's income?

On a Summary tab, put the first of the month in H2, then in I2 use =SUMIFS(Entries!$F$2:$F$500,Entries!$A$2:$A$500,H2). That adds everything actually received for the month. To check against what you expected, in J2 use =SUMIFS(Entries!$D$2:$D$500,Entries!$A$2:$A$500,H2). For one source, add a criterion: =SUMIFS(Entries!$F$2:$F$500,Entries!$A$2:$A$500,H2,Entries!$B$2:$B$500,"Rent").

A made-up month, worked out

Say the pension is ₹30,000, rent from a small shop is ₹12,000 and interest from a deposit that pays monthly is ₹3,500. Together they are 30,000 + 12,000 + 3,500 = ₹45,500. Bills due in the month total ₹38,000 on the bills tab. The margin is 45,500 − 38,000 = ₹7,500.

  • Pension: expected on the 1st, received on the 2nd, so 1 day late.
  • Rent: expected on the 5th, received on the 9th, so 4 days late.
  • Interest: expected on the 10th, received on the 10th, so 0 days late.

The late rent matters in this month because a bill of ₹8,000 was due on the 6th. The sheet tells you that a small cushion would have avoided a tight spot.

Because the received dates are logged next to the expected ones, the sheet also answers a question that is hard to answer from memory: which source is the least reliable? After six months, =AVERAGEIF(Entries!$B$2:$B$500,"Rent",Entries!$G$2:$G$500) gives the average delay for rent, and you can repeat it for pension and interest. Rent from a tenant who pays late every month is a different planning problem from a pension that lands on time.

What does the bills side look like?

Keep a Bills tab with the bill name, the due date and the amount, and a monthly total with =SUM(C2:C30). Add a column for the due day and sort by it. Next to the income dates, you can see which bills fall before the first credit of the month, which is where trouble starts. Link the margin at the top of the Summary tab: =I2-Bills!C31 if C31 holds the bill total.

When does a sheet stop being enough?

If the entries get long, or you hold several accounts, a sheet becomes tedious. SpendixAI can import statements in PDF, CSV or Excel, let you review rows, and place credits in income categories you create, with reports and a CSV download. Until then, the three tabs above do the job.

Add a Notes column to the entries tab for anything unusual: a pension that came in two parts, rent paid late with a reason, or interest that changed after a renewal. A one-line note written the day it happens saves a long search through statements in March. If any of the three sources stops or changes, keep the old rows and add new ones from the change date, so the history stays intact.

Monthly habits

  1. Add the three expected rows at the start of each month.
  2. Fill in received dates and amounts the day each credit lands.
  3. Look at the margin and the days-late column once a week.
  4. Once a year, review the expected amounts and dates after any change in pension, rent or deposits.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports