High School

Ruiz Company issued bonds on January 1 and has provided the relevant information. The Controller has asked you to calculate the bond selling price given two different market interest rates using Excel’s Present Value functions.

Use the information included in the Excel Simulation and the Excel functions described below to complete the task.

1. Cell Reference: Allows you to refer to data from another cell in the worksheet.
- Example: If in a blank cell, "=B2" was entered, the formula would output the result from cell B2, or $500,000 in this example.

2. Basic Math functions: Allows you to use the basic math symbols to perform mathematical functions.
- You can use the following keys: + (plus sign to add), - (minus sign to subtract), * (asterisk sign to multiply), and / (forward slash to divide).
- Example: If in a blank cell "=B3+B5" was entered, the formula would add the values from those cells and output the result, or 31 in this example.

3. PV Function: Allows you to perform the mathematical present value calculation of a value.
- Syntax: "=PV(rate, nper, pmt, [fv], [type])"
- Description:
- rate: Interest rate per period.
- nper: Total number of payment periods.
- pmt: Payment made each period (must be included if [fv] is not included).
- [fv]: Future value (must be included if pmt is omitted).
- [type]: Logical value of 0 or 1 (0: payment at end of period, 1: payment at beginning of period).
- Note: Include the [pmt] and [fv] arguments but leave out the [type] argument from the function. Enter the [pmt] and [fv] arguments as negative values.

4. IF Function: Allows you to test a condition and return a specific value if the result is true and a different value if the result is false.
- Syntax: "=IF(test_condition, value_if_true, value_if_false)"
- Description:
- test_condition: Evaluation of a cell's status (e.g., greater than, less than, equal to another number or cell).
- value_if_true: Returned value if the condition is true (could be another cell reference, a value, or text).
- value_if_false: Returned value if the condition is false (could be another cell reference, a value, or text).
- Note: If text is being used in the test_condition, value_if_true, or value_if_false arguments, it should be entered in quotations to be recognized as a "string of text."

Example: If in a blank cell "=IF(B2 > 250000, 'Cash is great', 'Cash is bad')" was entered, the formula would output the value_if_true result since the test_condition would result as true, or in this case, the text "Cash is great."

On January 1, Ruiz Company issued bonds as follows:
- Face Value: $500,000
- Number of Years: 30
- Stated Interest Rate: 7%
- Interest payments per year (Note: the bonds pay interest annually.)

Required:
1) Given the different market interest rates below, calculate the following items:
- Calculate the bond selling price USING THE EXCEL PV FUNCTION (fx). Note: Enter all function arguments as cell references.

a) Market Interest Rate: 9%
- Annual Interest Payment:
- Bond Selling Price:

b) Market Interest Rate: 5.5%
- Annual Interest Payment:
- Bond Selling Price:

Answer :

Final answer:

To calculate the bond selling price using Excel, the 'PV' function is applied taking into consideration the market interest rates of 9% and 5.5%, along with future values, payments, and the optional type argument.

Explanation:

In order to calculate the bond selling price of Ruiz Company, we will use Excel's PV function. Let's denote the bond's face value as $500,000, interest payments per year as 1, as the bonds pay interest annually, and stated interest rate as 7%.

For a bond with a market interest rate of 9%, the formula in Excel would be =PV(9%,30,-500000*7%,-500000). Similarly, for a market interest rate of 5.5%, the Excel formula would be =PV(5.5%,30,-500000*7%,-500000). The negative values are used for the payment and future value arguments because this represents outflow of money from your perspective (you are buying the bond, so you pay money for it initially and receive money in the future).

The results of these functions will represent the selling prices of the bonds at the respective market interest rates. The 'IF' function can be applied separately for decision making based on the calculated bond selling price.

Learn more about Excel PV function here:

https://brainly.com/question/34488254

#SPJ4

Final answer:

To calculate the bond selling price using Excel's PV function, input the relevant information into the function. Use the market interest rate, number of years, annual interest payment, and face value as arguments. Calculate the bond selling price for different market interest rates.

Explanation:

Bond Selling Price Calculation Using Excel's PV Function

To calculate the bond selling price using Excel's PV function, you need to input the relevant information into the function. The syntax of the PV function is "=PV(rate,nper,pmt,[fv])". In this case, the rate argument is the market interest rate, the nper argument is the total number of payment periods, and the pmt argument is the annual interest payment. The fv argument is the future value, which in this case would be the face value of the bond. By entering the appropriate cell references for each argument, you can calculate the bond selling price.

In the first scenario, where the market interest rate is 9%, the annual interest payment is $35,000 (calculated as 7% of the face value), and the bond selling price is the present value of the future cash flows. You can use the PV function to calculate the bond selling price by inputting the market interest rate, the number of years, the negative annual interest payment, and the negative face value as arguments.

In the second scenario, where the market interest rate is 5.5%, the annual interest payment is still $35,000, and the bond selling price can again be calculated using the PV function with the updated market interest rate, number of years, negative annual interest payment, and negative face value as arguments.

Learn more about Bond Selling Price Calculation here:

https://brainly.com/question/33151449

#SPJ12

Other Questions