Group Shared Expense Calculator – Free Travel Expense Sharing Template

Posted on
Report sheet with balances highlighted for one friend

This Group Shared Expense Calculator template can be used to calculate each person’s share of expenses in a group. For example, when you go on a trip with your friends where multiple people pay for shared expenses. At the end of the trip, you would need to calculate how much each person owes and to whom. Another example is if you participate in a group event which needs multiple purchases.

We were on a family trip last week for 3 days with a friend’s family. We shared expenses for car rental, hotel reservation, fuel, etc. and both families paid for expenses at different times. When we returned from the trip, we had to calculate our share of the expenses. I built this template to help with that.

Group Expense Shared Calculator - Report
Group Expense Shared Calculator – Report

 

FEATURES

  • Expenses can be split equally among all friends in a single step
  • Expenses can be split unequal among any number of friends
  • Expenses can be split unequal by % or Amount
  • Report shows all balances among friends
  • Calculates expense amounts for each friend

 

FREE DOWNLOAD

Group Shared Expense Calculator – Free Excel Template

VIDEO DEMO

 

HOW TO USE

Step 1: Enter friends’ names. The template allows up to 12 friends.

Expense Sharing - Enter Friends' Names
Expense Sharing – Enter Friends’ Names

 

Step 2: Enter the expense transactions

Enter shared Expense Transactions
Enter shared Expense Transactions

 

  • Date: Date of expense transaction for your reference (Not used in any calculation)
  • Transaction: Description of the transaction for your reference (Not used in any calculation)
  • Amount: Expense Amount
  • Category: Category of expense (Not used in any calculation)
  • Paid By: Person who paid for the expense
  • Split Type: There are three ways expense can be shared/split.
Three types of expense sharing or expense split
Three types of expense sharing or expense split

 

Let’s take expense for Coffee to illustrate the three split types.

Split Equal: When the expense is shared equally among all the friends.

Shared Expenses Split Equally among friends
Shared Expenses Split Equally among friends

 

This transaction does not need any further data entry. In this case, the $12 will be shared among all 8 friends equally. $.1.5 each.

 

Split Unequal – %: When expense is shared unequally among friends and we know the share in %, we can use this split type.  One example is when only 2 of the 8 friends had Coffee and their shares are 40% & 60%.  We enter the shares in appropriate columns named after the friends.

Shared Expenses Split Unequally among friends - %
Shared Expenses Split Unequally among friends – %

We will also use this split type when we have all the 8 friends sharing in unequal % share.

Note: when entering %, please enter in decimals. For example, enter 50% as 0.5.

 

Split Unequal – Amt: When expense is shared unequally among friends and we know the share in actual amounts, we can use this split type.  Following the same coffee example, when only 2 of the 8 friends had Coffee and their shares are $4 and $8.

Shared Expenses Split Unequally among friends - Amount
Shared Expenses Split Unequally among friends – Amount

 

Data Validation

There are some validation rules built in the template to ensure accurate data entry.

  1. Name entered in Paid By column should be present in the Friends list in the Home sheet
  2. When using ‘Split Unequal – %’, the % entered for all the friends should add up to 100%
  3. When using ‘Split Unequal – Amt’, the amounts entered for all the friends should add up to expense amount

 

If there are any errors, you will see it in the last column Validation.

Data Validation Column with error messages
Data Validation Column with error messages

 

You will also see visual indicators on the expense transactions.

Transactions with errors are highlighted
Transactions with errors are highlighted

 

Purple indicates transactions where the shares (% or Amount) are under the expenses and the Red indicates transactions where shares (% or Amount) are over the expenses.

Please ensure that there are no errors in order for the report to provide accurate balances.

Enter each transaction in a new row. If you are new to Excel tables, please read this article on Introduction to data entry in Excel.

Sample of Expense sharing entered
Sample of Expense sharing entered

 

Step 3: View Report 

Once we complete entering all the expense transactions, we can move to the report sheet.

Report sheet shows all balances among friends
Report sheet shows all balances among friends

Click to view full image

The sheet shows all the individual balances on who owes whom how much. We can also see the total amount a person owes and a person is owed to.

We can also select one friend to highlight that friend’s details and summary.

Group Shared Expense Calculator - Excel Template - Report Sheet
Group Shared Expense Calculator – Excel Template – Report Sheet

Click to view full image

The summary at the top shows Amount Owed To, Amount Owed From and Net Balance. The details show the Amounts Owed To, in Green and Amounts Owed From, in Red.

 

If there are any errors in data entry in the Expenses sheet, you will see a warning message in the Report sheet.

Error displayed in Report sheet about data validation issues
Error displayed in Report sheet about data validation issues

 

We can also see the amounts paid by each person, total expenses by each person and the net balance.

Paid Amount, Expenses & Balance for each friend
Paid Amount, Expenses & Balance for each friend

 

If the Net Balance is negative, that means the person will have to pay to others more than he will receive from them. Since we are distributing expense amounts equally, sometimes we end up with a rounding difference of a few cents.

 

Changing Currency

You can change the currency format displayed to suit your currency. Just select the currency cells and press Ctrl +1.

Changing Currency Format
Changing Currency Format

Choose the currency from the list and press OK.

 

If you find this template useful, please share the template with your friends. If you have any suggestions to improve, please post them in the comments.

 

Related Personal Finance Templates

XLYOURFINANCES – Review

Posted on

I came across this Excel workbook from Brad Hoffer (XLYourFinances), which will make finance management easier & I wanted to share it.

XLYourFinances
XLYourFinances

In Brad’s words,

“Xlyourfinances is an automated Excel spreadsheet that will give you control over your finances and save you time in the process. With import tools, auto-categorization and unlimited years of storage, this one spreadsheet is all you need for archiving, budgeting and mastering your personal finances for years to come.”

I had a chance to use this workbook and below is my review of the product.

 

PURPOSE

The workbook is designed to achieve the following.

  • Manage all your financial data in one place
  • Know the current balances in your accounts without much effort
  • Easily categorize your income & expenses and monitor
  • Set budgets, evaluate actual Vs budget to take corrective actions
  • Plan your financial future with balance forecasting
  • Additional finance tools such as loan comparison, mortgage schedule and financial statement

 

HIGHLIGHTS

  • Easy to get setup with your own accounts and personalized categories.
  • Allows comparing against your actual bank balance and identify any missing transactions.
  • Set up budget based on viewing trends in past years.
  • Enter transactions and evaluate how you are performing against budget overall and by category. This helps you take corrective actions in specific areas where you are over spending.
  • It is set up to handle data exported from your bank and credit card accounts. You can just copy and paste that data into this workbook, thereby saving time in data entry. It is flexible and can handle different banks’ export file formats.
  • The data will be with you in your own file in your computer. You are not sharing your account details or passwords with a third party.
  • Even if you are using a third party aggregator like mint.com, where all your financial transactions are collected in one place, you can use this workbook. This provides special import support for data exported from Mint.
  • Several smart features such as detecting duplicate transactions and flagging any potential wrong entry of a transaction.
  • Since it is in Excel, it is easy to copy/paste data in general and especially categorizing a lot of transactions in one go.
  • Easy to search and filter on transactions. You can even enter comments on transactions and search those comments.
  • Tons of Reporting which is also flexible
  • Lot of additional features: forecast your balances, compare loans, view the complete amortization schedule for your mortgage and generate personal financial statement.
  • Though there Is a lot of calculations and information in the workbook, Brad keeps it easier for the end user by highlighting cells (in yellow) that are user entries. There are help notes available throughout the workbook that you can view or hide when you need.
  • Brad presents a video to get started. I strongly recommend watching the video to become familiar with the workbook.

Overall, this is a very well-built application in Excel to manage your finances. It may seem complex at the beginning, but once you get used to the setup, you will reap the reward of getting control of your finances.

For more details, check it out at http://xlyourfinances.com/xlyf-budget-spreadsheet.html

Personal Finance Manager – Excel Template v2

Posted on

Hello Everyone,

I am glad to announce that the Personal Finance Manager template has been upgraded with the following key enhancements. The files are available for download in the same page.

  1. Increased number of accounts – Now can handle 20 bank accounts, 20 credit card accounts and 20 cash accounts.
  2. Increased number of categories and subcategories to unlimited
  3. Added new chart on account balance trendsPersonalFinanceManager_Excel_Template_Report_4
  4. Improved Year – Month slicers in the Report sheet. I am using calculated fields (Year, Month) in the Transactions sheet.
  5. A new version compatible with Excel for Windows 2007 and Excel for Mac 2011 has been added.

Please try the new version and provide feedback.

Personal Finance Manager (Free Excel Budget template)

Posted on

EXCEL BUDGET TEMPLATE

This is a simple free Excel template that focuses on making it easy for you to know what’s happening with your financial situation especially when you have multiple bank accounts, credit card accounts and cash. It also helps you set budgets and see how you are actually doing against your budget. With simple data entry, the template provides you instant access to actionable information in a consumable form that can answer key questions regarding your personal financial situation. Specifically, the template helps you in knowing the following:

  • How much money is in my different bank accounts?
  • How much do I owe on credit cards?
  • On what items am I spending my money on?
  • Am I exceeding my monthly budget? If so, in which categories?
  • How are my expenses trending over time?
  • Am I spending more on any specific expense category over time?
  • How much am I saving every month? How does that add to my net balance?

FREE DOWNLOAD


VERSION HISTORY

V2 launched March 2015:
Improved features (increased number of accounts to 20, increased number of categories and subcategories to unlimited, added new chart on account balance trends, version for Excel for Windows 2007 and Excel for Mac 2011) – Details Here

V1 launched May 2013


VIDEO DEMO

Click here to view on YouTube


HOW TO USE THE TEMPLATE

The template has 3 worksheets: 1) Settings 2) Transactions and 3) Report.

STEP 1: Enter information in Settings worksheet

  • Enter Accounts (bank accounts, Credit Card Accounts and Cash Accounts)
  • Set your starting balances of accounts
Personal Finance Management - Accounts Set up
Personal Finance Management – Accounts Set up

 

  • Create categories & Subcategories (trust me, this helps)
  • Set your monthly budgets
    Excel Budget Template - Set Categories and Budget

 

STEP 2: Entering transactions in the Transactions worksheet
When you open the template, there will be no records in the Transactions worksheet (as shown in the image below). Start entering your own transactions.

Enter Financial Transactions
Enter Financial Transactions

 

3 Types of Transactions:

  • Income and Expense: By default, all the Income and Expense transactions should be entered as positive amounts.
    • Special case (Refund): If you purchased an item at a store, you would enter an Expense transaction with positive amount. If, a few days later, you returned the item to the store for some reason and get a refund, then you should enter the refund as a new Expense transaction with negative value.
  • Transfer: When money is transferred from one account to another, create two records
    • ‘Transfer’ type with negative amount from the account you are taking the money from.
    • ‘Transfer’ type with positive amount for the account you are depositing the money into.
    • Examples of Transfers are Credit Card Payment (transfer from Bank account to Credit Card account) and ATM withdrawal (transfer from Bank account to Cash)
  • Drop down menus are available for easy data entry in these fields (Type, Category, SubCategory, Account).

After you enter your transactions, the Transactions worksheet would look like this image below.

Sample Money Transactions
Sample Money Transactions
STEP 3: View ‘Report’ worksheet
Since there are pivot tables and charts, please refresh the data by going to Data ribbon and refresh all (or keyboard shortcut Ctrl+Alt+F5) . This updates the charts with your new transactions.
How to refresh Report in the Excel Budget Template
How to refresh Report in the Excel Budget Template

Report sheet is locked to prevent accidental editing of formulas. To unlock, use password indzara

The report has four pages.

1) Summary

  • Summary of your current financial status
  • Current balance in each of your accounts
    Personal Finance Manager Excel - Report - Summary of Accounts

 

You can find balances for any period in each of your accounts

Choose Bank Account in Report
Choose Bank Account in Report

 

This can be helpful when your bank statements and credit card statements actually have their billing cycles different from calendar months. This allows you to compare your statements with the data you have in this template and confirm that you have not missed any transactions.

Transaction Summary for chosen Period - Beginning and Ending Balance
Transaction Summary for chosen Period – Beginning and Ending Balance

 

The chart shows the trend of month-end balances in the account chosen.

Month end Balances of finances
Month end Balances of finances

 

2) Monthly Financial Analysis

Please choose one month at a time using the slicers at the top.

View total Income, expense and savings

Excel Budget Template - Income, Expense and Savings compared to budget
Excel Budget Template – Income, Expense and Savings compared to budget

 

Understand where the money came from and where it was spent, in the month

Monthly Income Categories Breakdown and Expense Categories Breakdown
Monthly Income Categories Breakdown and Expense Categories Breakdown

 

  • Track whether you have exceeded the monthly budget.
  • Also see the comparison of expenses to budget by each category
  • Categories that exceeded budget will be highlighted in red
Compare Monthly Expense in each Category against Budget Set
Compare Monthly Expense in each Category against Budget Set

 

Understand expenses by Subcategories.

Understand Expenses by Subcategories
Understand Expenses by Subcategories

 

3) Trends of expenses, savings and net balance

Trend of expenses over time and comparing against monthly budget

Excel Budget Spreadsheet - Are my Expenses under Monthly Budget?
Excel Budget Spreadsheet – Are my Expenses under Monthly Budget?

 

Trend of savings and net balance over time

Excel Personal Budget Spreadsheet - Track Savings every Month and Net Balance/Worth
Excel Personal Budget Spreadsheet – Track Savings every Month and Net Balance/Worth

 

4) Trends of transactions by Categories and Sub Categories

Trends of transactions by Categories and SubCategories
Trends of transactions by Categories and SubCategories

 

I hope you find this template useful in managing your personal finances easily.

For more excel templates on money management, please visit https://indzara.com/personal-finance-free-excel-templates/

Loan Comparison and EMI Payment Calculator (Excel template)

Posted on
This is a simple template to help one in decision making on loans. There are two version of this template, one using the Indian Rupees as the currency and the other one without any currency formatting. Both versions function in the exact same way and hence the following information applies to both versions.
Loan_Payment_EMI_Calculator_Template

DOWNLOAD

 



Purpose of the template

  • This template calculates
    • 1) EMI
    • 2) Total Interest paid during the duration of the loan
    • 3) Last Payment Month
    • 4) Number of Payments
  • It allows you to compare three different loan scenarios based on their EMI, Interest, Last Payment Month and Number of Payments.
    • For example, you can compare loans from three different banks if they have different interest rates or loan period.
  • You can also compare how much savings you can get if you pre-pay your loan. You can enter up to 12 pre-payments for each loan.
  • You can print the loan comparison and the detailed payment schedule (Amortization schedule).

 

How to use the template
Step 1: Enter information about loans. You can enter up to 3 loans if you would like to compare. If you just want to calculate EMI for one loan, please enter in only one of the scenarios.
LoanInformationInput
In this example we are comparing three scenarios:
  1. Loan of 20 lakhs at 11% interest rate
  2. Loan of 20 lakhs at 10% interest rate
  3. Loan of 20 lakhs at 10% interest rate with pre-payments made during the course of the loan

Step 2: Enter details of pre-payments if you are planning to pre-pay. Otherwise, please leave this empty. The image below shows the pre-payments entered in Scenario 3.

Pre-Payments_Input

Step 3: See the Summary section where you can see and compare the EMI, Total interest, Last loan payment month and number of loan payments. We can clearly see how Scenario 3, where you make pre-payments, leads to interest savings.

LoanComparison_Summary

Interest_EMI_Comparison

Step 4: See the Loan Payment and Amortization Schedule section to see the month by month details on interest paid, principal paid and remaining balance.

 LoanPayment_Amortization_Schedule

Assumptions/Settings
  • Assumes monthly loan payments.
  • Loan Period can be up to 30 years.
  • EMI (Equated Monthly Installment) calculation is for the end of the period. In the example above, loan begins on June 1, 2013. First payment is due on July 1, 2013 (end of first period).
  • All payment dates are on the first day of each month.
  • EMI is rounded up to the nearest paisa (2 digits).
  • Assuming no penalty for pre-payments.
  • Pre-payments (also called as extra payments) can happen any time during the loan tenure.
  • If pre-payments exceed the remaining balance, the extra amounts (in the pre-payments) will not be added to the payments.

I hope you find this helpful when you are trying to make the decision on your loans (home loans, property loans or auto loans). There are many EMI calculators online however this template allows you to compare multiple loan scenarios in one view and also allows pre-payments to be entered.


I look forward to your feedback.
LoanComparison_Payment_Page1
Loan_Information_Input
Summary

Interest_EMI_Charts

Loan_Payment_Amortization_Schedule