2023: Week 49 - Regular Savings Accounts
Challenge by: Jenny Martin
Data Source Bank (DSB) are looking to introduce a Regular Savings Account for their customers. There are 2 factors they need to decide:
- The interest rate
- The maximum amount a customer is able to pay into the account each month
DSB are looking to compare other 12 month products currently in the market, to help decide these factors. They want to understand which account offers the customer the biggest opportunity to increase their savings. They also want to educate their customers by showing how their savings would increase each month.
Input
1 file containing the details of 12 month regular savings accounts currently available in the UK Market as taken from Money Saving Expert
Requirements
Note: We assume the interest will be spread evenly across the 12 months for this challenge
- Input the data
- Update the Max Monthly Amount so it is a numeric field
- Update the Provider field so it is easy for customers to see in 1 field which accounts have additional conditions
- Ensure we have a row for each month for each account i.e. 12 rows for each account
- Calculate the savings value in each account, each month after interest has been applied
- Assume a customer is able to pay in the Max Monthly Deposit each month
- Compound interest may come in useful (formula taken from Wikipedia)
- Hint: think about each month's deposit and how many months it will collect interest before the end of the 12 months
- Create a field for the maximum possible savings a customer could get from each account
- Calculate the maximum total interest a customer could get from each account
- Rank the accounts by the maximum possible savings
- Rank the accounts by the maximum total interest
- Round all monetary values to 2 decimal places
- Output the data
Output
- 9 fields
- Providers Ranked by Max Savings
- Providers Ranked by Total Interest
- Provider
- Interest
- Max Monthly Deposit
- Month
- Savings each month
- Max Possible Savings
- Total Interest
- 144 Rows (145 including headers)
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1
You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!