Explore BrainMass
Share

# Finance Calculations using Excel: Future Value and Present Value

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

1. You want to buy a car, and a local bank will lend you \$ 20,000. The loan would be fully amortized over 5 years ( 60 months), and the nominal interest rate would be 12%, with interest paid monthly. What is the monthly loan payment? What is the loan's EFF%?
2. Your parents will retire in 15 years. They currently have \$ 230,000, and they think they will need \$ 1 million at retirement. What annual interest rate must they earn to reach their goal, assuming they don't save any additional funds?
3. If you deposit \$ 12,000 in a bank account that pays 8% interest annually, how much will be in your account after 5 years?
4. What is the present value of a security that will pay \$ 85,000 in 20 years if securities of equal risk pay 4% annually?
5. Assume that 1 year from now you plan to deposit \$ 1,000 in a savings account that pays a nominal rate of 8%.
a. If the bank compounds interest annually, how much will you have in your account 4 years from now?
b. What would your balance be 4 years from now if the bank used quarterly com-pounding rather than annual compounding?
c. Suppose you deposited the \$ 1,000 in 4 payments of \$ 250 each at the end of Years 1, 2, 3, and 4. How much would you have in your account at the end of Year 4, based on 8% annual compounding?
d. Suppose you deposited 4 equal payments in your account at the end of Years 1, 2, 3, and 4. Assuming an 8% interest rate, how large would each of your pay-ments have to be for you to obtain the same ending balance as you calculated in part a?

#### Solution Preview

Your tutorial is in excel, attached, and uses the PV, FV, PMT functions except for two computations that ...

#### Solution Summary

Your tutorial is in excel, attached, and uses the PV, FV, PMT functions except for two computations that cannot be solved with these. Those two have an amortization schedule or an IRR computation to arrive at the needed amounts. The computations are all done pointing to the data cells and so this is a template for other similar problems. Changes to the data will update the answers. Click in cells to see calculations.

\$2.19

## Finance Problems (Fixed costs, Net present value, yield to maturity and more)

Below is a wide range of Finance problems. There are a total of 8 problems. Please show all work so I can get an understanding of how you solved each problem.

1.) Dominic's Italian Cafe sells pizzas at \$18.00 each. Fixed costs total \$12,000 per month and Dominic's variable costs total \$6.00 per pizza. Calculate the breakeven point in pizzas per month for Dominic's.

2.) At the end of January, your company had an inventory of 825 units, which cost \$12 per unit to produce. During February the company produced 75 units at a cost of \$16.00 per unit. If your firm sold 1,050 units in February, what is your cost of goods sold: A. assuming LIFO inventory accounting and B. assuming FIFO inventory accounting?

3.) Your company decides to issue \$1,000 par value 12% bonds. Compute the current price of the bonds if the percent yield to maturity is A. 8% or B. 18%.

4.) If you save \$1,500 per year how much will you have in A. 7 years at 10% annual interest and B. 15 years at 12% annual interest?

5.) What is the present value of lottery prize of \$1,000,000 payable in 20 annual installments of \$50,000 each assuming a discount rate of 12%?

6.) You accepts a job and the company is willing to pay you a sign on bonus and you must choose between 3 alternative payment plans: Plan A - \$30,000 payable today, Plan B- \$4,000 payable 3 years from today or Plan C, annual payments of \$1,225 each with the 1st payments 1 year from today. Assuming a discount rate of 8%. Which bonus should you accept?

7.) You are considering the purchase of a new machine that would increase the speed of manufacturing and save money. The net cost of this machine is \$66,000. The annual cash flows have the following projections: Year 1: \$21,000, Year 2: \$29,000, Year 3: \$36,000, Year 4: \$16,000 and Year 5 \$8,000. A. What is the payback period? B. If the cost of capital is 10%, what is the net present value?

8.) You must choose between two bonds. Bond A pays \$90 annual interest and has a market value of \$850. It has 10 years to maturity. Bond B pays \$80 annual interest and has a market value of \$900. It has 2 years to maturity. A. Computer the current yield on both bonds. B. Compute the yield to maturity on both bonds.

Please show all work so I can understand how you solved the problem. Answers on an Excel worksheet is fine. Thank you.

View Full Posting Details