Microsoft Excel for Accounting FOR EVALUATION ONLY © …

Microsoft Excel for Accounting FOR EVALUATION ONLY

? 2017 Labyrinth Learning ? Not for Sale or Classroom Use

8Bond Amortization

LY N OLEARNING OBJECTIVES

Create a bond amortization schedule

N Use the PV, FV, and PMT functions

ATIO

Protect worksheet elements

Automate processes with macros

Financial functions are among Excel's more complex elements. However, it's worth taking the time to master their use, as these

U functions can save substantial time. Commonly used financial L functions are present value (PV), future value (FV), and payment A (PMT). Because of the complexity of these functions, you may find

it necessary to protect some or all of a worksheet that includes

Vthem. You may also find that automating processes involving these

functions with macros allows for a greater efficiency. In this chapter,

Eyou will use financial functions while creating a bond amortization schedule. You will begin by calculating different features of a bond using PV, FV, and PMT functions. You will create a bond

amortization schedule for this bond and protect various worksheet

elements. Last, you will create a macro to automate the completion

of a bond amortization table.

Labyrinth Learning

225

Microsoft Excel for Accounting FOR EVALUATION ONLY

226 Excel for Accounting Chapter 8: Bond Amortization

? 2017 Labyrinth Learning ? Not for Sale or Classroom Use

PROJECT

City Music World

City Music World is a retail store that sells musical equipment and offers lessons for beginning and experienced musicians. The company is considering issuing bonds to fund a potential expansion of its operations. Before doing so, the company wants to

Y review the necessary payment schedule and purchase price of bonds with different

characteristics. You are creating a bond amortization schedule that can be updated for

L different bonds. You will protect key worksheet elements and automate as much of the N schedule completion as possible.

In this chapter, you will create a bond amortization schedule using PV, FV, and PMT functions. You will also apply different levels of worksheet protection for different users.

O Last, you will create macros to automate populating the bond amortization schedule

once key bond data has been entered.

N ATIO LU A EV

This bond amortization schedule has been fully populated through the use of a macro that automates the creation of all formulas.

Labyrinth Learning

Microsoft Excel for Accounting FOR EVALUATION ONLY

? 2017 Labyrinth Learning ? Not for Sale or Classroom Use

Accounting Refresher: Bond Amortization Schedule 227

Bond Amortization Schedule

When a business seeks to raise cash to fund a large expenditure, such as for the building of a new facility or the development of a new product, it will typically raise

Y capital by issuing additional shares of stock (equity

financing) or borrowing money (debt financing).

L One financial instrument that can be used when pursuing N debt financing is a bond.

When a business issues a bond, it receives a lump payment from the bondholder to be used by the business

O to pursue its intended expenditure. In exchange, the

bondholder receives periodic payments over the life of the bond (interest payments) and a lump-sum payment equal

to the face value of the bond at the end of the bond's

N life (principal payment). To determine figures such as the

initial selling price of the bond and interest payments, you must understand the various bond characteristics. When using the most commonly applied effective interest method,

NOTE!

these characteristics include the face value, life of the bond, contract interest rate, and effective interest rate.

O The effective interest method is the amortization method examined here, as it requires the completion of a I bond amortization schedule. The alternative, though far less widespread, is to use straight-line amortization.

T Face Value

The face value of a bond is the amount paid to the bondholder on the maturity date

A (the end of the bond's life). If the issue price of the bond equals the face value, then

the bond is being sold at its par value. However, oftentimes bonds are sold at more than their face value (sold at a premium) or less than their face value (sold at a discount).

Life of the Bond The life of the bond represents the period of time over which the bond will be

Uoutstanding before it matures. A bond is often held by a single bondholder over the Lcourse of its life, but it's also common for bonds to be sold from one bondholder to

A

another. Periodic interest payments are made to the current bondholder throughout the life of the bond, and the face value of the bond is paid to the bondholder upon bond maturity. Note that the bond will indicate the frequency with which interest payments

EV

are made (annually, semiannually, etc.), and this schedule is followed throughout the life of the bond.

Contract Interest Rate vs. Effective Interest Rate The contract interest rate is attached to the bond and used to calculate interest payments periodically made to the bondholder. Conversely, the effective interest rate

(also called the market interest rate) factors in the impact of compounding on the

interest payments. When completing a bond amortization table, the contract interest

(continued)

Labyrinth Learning

Microsoft Excel for Accounting FOR EVALUATION ONLY

228 Excel for Accounting Chapter 8: Bond Amortization

? 2017 Labyrinth Learning ? Not for Sale or Classroom Use

rate is used to calculate actual interest payments, while the effective interest rate is used to calculate the present value of the bond.

These interest rates can be used to quickly identify whether a bond will be sold at a discount, face value, or a premium. When the contract interest rate and effective interest rate are the same, the bond sells at face value. If the contract rate is below the effective rate, then the bond can be considered less desirable than a similar bond that

Y could be purchased elsewhere. This bond will sell at a discount from the face value. L Conversely, if the contract rate is above the effective rate, then the bond is considered N more attractive than a similar bond that could be purchased elsewhere and will sell at a

premium above face value.

Amortizing Bond Discount or Premium

O When a bond is issued at a discount or premium, the impact of this discount or

premium within the financial records of the bond issuer is spread over the life of the bond. This bond amortization either increases the interest expense recorded when each bond payment is made (in the case of a discount) or reduces the interest expense

N (in the case of a premium). The total interest expense for each period is calculated as the

interest payment plus amortized discount or minus amortized premium.

Carrying Value The carrying value of a bond can be calculated as face value minus unamortized discount (or plus the unamortized premium). When the bond is issued, the unamor-

O tized discount or premium equals the total discount or premium for the bond. As I the discount or premium is amortized, the unamortized portion of the discount or

premium is gradually reduced (leading to a gradual increase in the carrying value when

T there is a discount or a gradual decrease when there is a premium). The result is that A once the bond matures, the discount or premium will have been amortized to $0, and

the bond's carrying value will equal its face value.

LU Working with Financial Functions AExcel includes a variety of financial functions that can simplify the creation of accounting-related

worksheets. For example, there are basic financial functions for determining monthly payments on loans, total interest paid on loans, and the future value of investments. When examining the charac-

Vteristics of a specific bond or other financial instrument, the PMT, PV, and FV functions are useful. EPMT Function

For the PMT function to calculate the required payment for a bond, you must specify the bond interest rate, number of payments to be made, present value, and future value of the payment at maturity.

Labyrinth Learning

Microsoft Excel for Accounting FOR EVALUATION ONLY

? 2017 Labyrinth Learning ? Not for Sale or Classroom Use

Working with Financial Functions 229

The interest rate and number of payments must be carefully considered. The effective interest

rate (not the contract interest rate) is the first argument. It's typically expressed in annual

terms, so if a bond requires an alternative payment schedule (such as semiannual instead of

annual payments), the interest rate entered in the formula must be adjusted. For example, if

the effective interest rate is 14% and a bond requires semiannual interest payments, then there are two interest payments every year. You divide 14% by two payments per year to arrive at an interest rate of 7%, which is what is entered as the first argument in the function.

Y The number of payments will often differ from the number of years in the life of the bond. If L a bond with semiannual interest payments has a five-year life, then it will have a total of ten

payments (five years * two payments per year).

One additional element to review is how the PMT function expresses periodic payments. By default, the

N

O result of the PMT function is displayed as a negative

number. When writing the formula it is common to place a negative sign before the function. This reverses the result from a negative to a positive figure. Similar consideration is given to

the individual arguments of the PMT function. Because the FV argument represents a lump

N payment at the end of the bond's life, it should also be expressed as a negative number when

writing the formula.

FormulasFunction LibraryInsert FunctionFinancialPMT Function

IO PV Function and FV Function T The PV function calculates the present value of a bond when you specify the bond interest rate,

number of payments, interest payment amount, and future value of the payment at maturity. The FV function calculates the future value of a bond when you specify the bond interest rate,

A number of payments, interest payment amount, and present value.

The PV and FV functions typically require negative symbols in their formulas.

U The same considerations must be made for these functions as are made for the PMT function. LBoth the effective interest rate argument and the number of payments argument are entered

Aper the earlier discussion. Additionally, the interest payment amount and future value of the payment at maturity must be expressed in negative terms. For the FV function, then, a negative sign is added before the FV at the beginning of the formula. The present value is consid-

V

ered to be a positive amount in Excel (since it does not represent a payment), so a negative sign is not needed before the PV at the beginning of that formula.

E

FormulasFunction LibraryInsert FunctionFinancialPV Function FormulasFunction LibraryInsert FunctionFinancialFV Function

Labyrinth Learning

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download