Class 2. General task – develop a workbook that calculates interest in a bank account.
Data
Prepare a list of 20 example transactions in a bank account in the following layout:
No. Date Value Category
1 2-Feb-2015 $800,00 Scholarship 2 4-Feb-2015 -$100,00 Food
… … …
Transactions should be ordered by dates, starting in February 2015. Two out of twenty transactions should occur in the same day.
Functionality and assumptions
• Interest rate is fixed and stored as a parameter in a separate cell.
• account balance after each transaction, necessary for calculation of interest, should be calculated within the formula for interest calculation and not in a separate cell
• There is no capitalization of interest.
• Columns with auxiliary calculations are NOT allowed.
• Provide that the account balance never turns negative (by setting the opening balance high enough).
• The worksheet always displays the current date, current balance and the current sum of interest (earned from the first day of transaction record).
• The user should be able to add new transactions (date, value, category descrtiption), while the results adjust automatically. A total of 10 thousands transactions should be allowed, while initially we provide only 20 transactions.
Hint – it will be helpful to calculate the balance of the account after each of the transactions.
Layout
Make the layout clear and convenient for the user :
• Distinguish the user-input cells (e.g. by color) from the calculated cells.
• Use the proper formatting (e.g. currency format for transactions, etc.).
• The interest rate, current date, current balance and current sum of interest (i.e. interest earned from the first day of the account records to the current day) should be always visible at the top of the worksheet.
• Prevent calculations from being modified by the user (cell protection).
• Columns with auxiliary calculations (if any) should be hidden in the final version of the workbook.
• Calculations and serial number in a given row results are hidden until a new transaction is added (use the IF function).
Validity checks
Include validation mechanisms ensuring that:
• The date of a new transaction is not earlier than the date of the previous transaction.
• Transaction category (description) should be chosen from a list.
• No future transaction dates are allowed.
Advanced version (for further classes)
Allow (1) a variable interest rate, and (2) interest capitalization on the 1st of each month (develop a new workbook).
Explanation – calculating interest in the bank account
Suppose we initially have $2000 in our bank account, on day 1 – this is the initial balance.
Assume the annual interest rate is 5%. This implies the daily interest rate of 5%/365 = 0.014%. It means that each day we earn 0.014% of what we have in the account (i.e. 0.014% of the account balance).
It is convenient to assume that interest is earned overnight – for example at the beginning of day 2 you receive interest according to the balance you had at the end of the previous day.
Assume further that in the next two days there are no outflows or inflows to the account. This means that on day 3 we will still have $2000 in the account. For a period in which the account balance is fixed ($2000 in our case) we can calculate interest according to the following formula:
Interest = Account_balance * Number_of_days * Daily_interest_rate With our assumed numbers this yields:
Interest = $2000 * 3 * 0.014% = $0.84
Note we count three days (or nights) on which the balance was fixed. The interest is due at the beginning of day 4.
So until day 4 we earned 84 cents of interest (in this calculation we use daily interest rate rounded to three decimal points; you can verify that a more exact spreadsheet calculation would actually yield $0.82).
Now assume on day 4 we receive income of $1000. The end-of-day balance is therefore equal to $2000 + $1000 =
$3000. Suppose today is day 30. How much interest have we earned between day 4 and 30? Between day 4 and 30 26 days (or nights) elapsed, therefore we have:
Interest = $3000 * 26 * 0.014% = $10.92
Total interest earned until day 30 (i.e. between from day 1 to 30) is:
$0.84 + $10.92 = $11.76
