The Acme Company has its European subsidiary based in the Euro zone. The subsidiary must solve a series of five problems that require you to apply the concept of "time value of money," or TVM. The five problems are listed below. Solving them will require the use of Microsoft Excel. Before you begin your work, each student is to select a unique nine-digit random number that contains no zeros, no "patterns," and should use most of the digits between one and nine. This value will be referred to as the student unique number (SUN). Further, digits within the SUN are read from left to right. For example, if the SUN = 123456789, the first digit = 1, the second digit = 2, etc. Please note that the interest rate used in all questions represents an annual rate and all Euro figures are in whole Euros (not Euros and cents).
Acme plans to construct a new manufacturing facility in 14 years. If Acme estimates that today's cost of the new plant is SUN Euros (use all 9 digits) and annual inflation is A% (A = the first digit of SUN), how much will the manufacturing plant cost in 14 years?
Acme has decided to establish a sinking fund for its outstanding preferred stock issue. SUN (use all 9 digits) represents the amount of the issue that will be retired in 26 years. At the beginning of each of these 26 years, Acme will deposit an equal amount into an account that earns B% (B = the second digit of SUN). What is the value of this periodic deposit?
One of Acme's new projects will generate the following cash flows at the end of each of the stated years: year 2 = SUN digits 1-3; year 4 = SUN digits 4-6; year 7 = SUN digits 7-9. If these cash flows are discounted at 12%, what is the sum of their present values?
Acme is assessing its employee pension fund. At the end of each of the next 25 years Acme will have to pay its retirees (use the first 4 digits of your SUN). If the fund is estimated to earn D% (D = the fourth digit of SUN), how much does Acme need to have set aside today to ensure that it can meet its future obligations? (At the end of the 25th year, the balance should be drawn down to zero.)
As part of a new labor contract, Acme has agreed to make a one-time contribution of Euros 1,000,000 to the construction of a new physical fitness facility for its employees. This amount will be placed in an account earning E% (E = the fifth digit of SUN). When the account grows to Euros 1,850,000, construction will commence. How long must employees wait until construction of their new gym begins?
Very creative way to learn to use EXCEL for typical financial calculations. This is more of a "how to use" EXCEL problem than a finance problem. And would be quite difficult to to here in Jishka.
Repost if you have a specific question.
Hint: put your SUN in A1
In B1 put @int($A$1/100000000) which should produce the 1st digit in SUN
In C1 put @mod(@int($A$1/10000000),10) which should produce the 2nd digit in SUN. Repeat the method in C1 to produce the rest of the digits in SUN
To solve these problems using the concept of the "time value of money" (TVM), we will need to use Microsoft Excel. The first step is to select a unique nine-digit random number, called the Student Unique Number (SUN), which contains no zeros, no patterns, and uses most of the digits between one and nine.
Now, let's solve each of the five problems one by one:
1. Cost of the manufacturing plant in 14 years:
To calculate the cost of the manufacturing plant in 14 years, we need the current cost (SUN Euros) and the annual inflation rate (A% - the first digit of SUN). We can use the TVM formula to calculate the future value:
=FV(A/100,14,0,-SUN)
Here, A/100 represents the annual inflation rate as a decimal, 14 represents the number of years, 0 represents the regular payment that we do not have, and -SUN represents the initial investment or current cost of the plant.
2. Value of the periodic deposit for the sinking fund:
To find the value of the periodic deposit for the sinking fund, we need the retiring amount (SUN Euros) and the interest rate (B% - the second digit of SUN). We can use the TVM formula to calculate the periodic deposit:
=PMT(B/100,26,0,SUN)
Here, B/100 represents the interest rate as a decimal, 26 represents the number of years, 0 represents the future value that we want to reach (retiring amount), and SUN represents the present value of the preferred stock issue.
3. Sum of the present values of cash flows:
To find the sum of the present values of the cash flows, we need the cash flows for specific years (stated by SUN digits) and the discount rate (12%). We can use the TVM formula to calculate the present values of individual cash flows and then sum them:
=PV(12/100,2,0,-SUN(1-3)) + PV(12/100,4,0,-SUN(4-6)) + PV(12/100,7,0,-SUN(7-9))
Here, 12/100 represents the discount rate as a decimal, 2 represents the year for the first cash flow, 4 represents the year for the second cash flow, 7 represents the year for the third cash flow, and -SUN(1-3), -SUN(4-6), -SUN(7-9) represent the individual cash flow amounts.
4. Amount to be set aside for the employee pension fund:
To calculate the amount Acme needs to have set aside for the employee pension fund, we need the future payment amount (first 4 digits of SUN) and the fund's estimated earning rate (D% - the fourth digit of SUN). We can use the TVM formula to calculate the present value of future payments:
=PV(D/100,25,0,-SUN(1-4))
Here, D/100 represents the earning rate as a decimal, 25 represents the number of years, 0 represents the future value that we want to reach (zero at the end of the 25th year), and -SUN(1-4) represents the future payment amount.
5. Time required for the account to reach Euros 1,850,000 for gym construction:
To calculate the time required for the account to reach Euros 1,850,000 for gym construction, we need the initial contribution (Euros 1,000,000) and the interest rate (E% - the fifth digit of SUN). We can use the TVM formula to calculate the number of periods required:
=NPER(E/100,Euros 1,850,000,-Euros 1,000,000,0)
Here, E/100 represents the interest rate as a decimal, Euros 1,850,000 represents the future value that we want to reach, Euros 1,000,000 represents the initial contribution, and 0 represents the periodic payment that we do not have in this case.
By plugging in the respective values into these formulas in Microsoft Excel, you should be able to get the solutions to the five problems.