BlockIF
| 10 points | Create a UDF that uses a BlockIf to determine the interest rate as a function of the deposit amount. |
| Format the rate as a percentage as part of the UDF | |
| A bank offers different yealy interest rates to its customers based on the size of the deposit: | |
| Deposit | Rate |
| < 1000 | 2.00% |
| 1000< Deposit < 10,000 | 2.50% |
| 10,000 < Deposit < 100,000 | 3.00% |
| > 100,000 | 4.00% |
| Deposit | Rate |
| $4,000 | =DepositRate(C13) |
| $8,000 | |
| $12,000 | |
| $100,010 |
NestedIf
| 10 points | NestedIF logic | |
| Create a UDF which will provide the rate of interest for a certificate of deposit. | ||
| 1. The interest rate depends on both size of the deposit and the length of the certificate of deposit. | ||
| a. If the maturity of the purchased CD is greater six months and larger than $15,000 , the investor earns an additional 0.5% over the base rate. | ||
| For example: if the deposit is larger than $15,100 and has a maturity of 2 years then the rate is 5.5%. | ||
| 2. Format as a percentage | ||
| Deposit Size | Base rate | |
| < $10,000 | 2.0% | |
| $10,000 < n ≤ $15,000 | 3.0% | |
| > $15,000 | 5.0% | |
| Deposit | Maturity years | Rate |
| $9,000 | 0.25 | =CDRate(Deposit,Maturity) |
| $11,000 | 0.25 | |
| $16,000 | 0.333 | |
| $9,000 | 0.75 | |
| $11,000 | 0.75 | |
| $16,000 | 1 |
SimpleLoop
| 10 points | Write a UDF that will add the cash flows in the column. |
| Format the sum as a $, no decimals, within the UDF. | |
| Range should be dynamic….can add additional rows and UDF continues to apply to the new dataset | |
| CF | |
| $500 | |
| $600 | |
| $200 | |
| =AddCF(B4:B6) |
UDFLoop
| 10 points | |
| The project described requires a $100,000 investment, and provides cash flows at the end of years 1, 3, and 7. | |
| · Create a UDF that will discount each cash flow and then sum these discounted cash flows | |
| · Assume a discount rate of 10%. | |
| Year | CF |
| 0 | $ (100,000) |
| 1 | $ 50,000 |
| 3 | $ 60,000 |
| 7 | $ 45,000 |
| =DISCF(C6:C9, B6:B9) |
SubLoopWithCalculation
| 5 points | ||||
| Prices | ||||
| Company | 5-Dec-17 | 5-Dec-16 | PeriodReturn | Required |
| WFC | 57.65 | 52.85 | 1. Create a subroutine which loops through the price data and calculates return. | |
| FTNT | 41.26 | 30.22 | a. the returns should be placed in column D. | |
| KMB | 123.48 | 110.17 | b. You must loop through the prices to compute the returns | |
| F | 12.61 | 11.78 | c. the loop must be dynamic, that is, I could add an additional stock and your macro continues to work. | |
| FB | 173.7 | 117.43 | d. Find a simple average of the returns–place in D11 | |
| Portfolio Average | 2. Insert a button control to run the macro (in D1) |
LoopWith2Variables
| 5 points | ||
| Find the future value of cash flows if the cash flows and rate differ each year. | Assume beginning-of-year deposits. | |
| For example, below, a$ 1000 deposit occurs on January 1 and earns 10% during 2017. | ||
| A second deposit occurs on January 1, 2018 and the bank pays 8% during 2018 for all deposits ( including the 2017 deposit.) | ||
| No deposits occur during 2019, so the account continues to earn 8% during 2019. | ||
| A deposit occurs on January 1, 2020. The rate earned in 2020 is 12%. | ||
| Insert a button to run the routine, in C1. | ||
| The routine should be dynamic…allow for additional deposits. | ||
| What is the value of the deposits in this account as of January 1, 2021? | ||
| YR | Deposit | Rate |
| 2017 | $1,000 | 10% |
| 2018 | $5,000 | 8% |
| 2020 | $4,000 | 12% |
