fin315
qwertyui
StudentFinal.xlsxBlockIF
| 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% | ||||||
| . |
