fnb100f261029
Watch on YouTube →
Overview
The finance lesson moves from future value of annuities to present value, showing how Excel's RATE function can solve for an unknown periodic rate and how logarithms can solve for the number of periods. It applies present-value analysis to a lottery choice between $6 million today and $20,000 monthly for 30 years: at a 6% APR the annuity is worth about $3.335 million today, while at a 1% APR it is worth about $6.2 million.
Key takeaways
- Excel's RATE function solves for an annuity's periodic rate by iterating over possible values; enter the payment as negative so Excel recognizes it as a cash outflow.
- Annuity timing changes the result: ordinary-annuity payments arrive at period-end, while annuity-due payments arrive at period-start and receive one additional compounding period.
- When an annuity's period count appears in an exponent, natural logarithms can solve for n; the worked example converts 60 monthly periods into five years.
- A $20,000 monthly lottery payment for 360 months totals $7.2 million nominally, but the installments must be discounted before comparison with a $6 million lump sum today.
- Under the example's assumptions, the $20,000 monthly annuity has a present value of about $3.335 million at 6% APR and $6.2 million at 1% APR, demonstrating how strongly discount rates affect payout choices.
- Expected inflation, borrower default risk, and required real returns influence market interest rates; higher discount rates reduce the present value of distant annuity payments.
Chapters
- Future value of an annuity describes regular deposits, such as contributions to a graduate-school fund.
- Present value of an annuity describes the value today of a stream of borrower payments or lender receipts.
- Any annuity variable can be unknown, including future value, payment, periodic rate, or number of periods.
- The example sets a $60,000 future-value goal, a five-year timeline, and $860 deposits, then asks what rate satisfies the target.
- Unlike the other annuity variables, the rate has no single rearranged formula that directly solves for it.
- Manual trial and error across decimal rates is inefficient, so computers iteratively test candidates and narrow the range.
- In Excel, open the Financial functions list and select RATE to solve for the periodic rate.
- Enter the number of periods, payment, and future value; leave present value blank for this multiple-payment annuity problem.
- Set Type to 0 or blank for an ordinary annuity, and to 1 for an annuity due, whose payments receive one extra compounding period.
- Enter the payment as negative to mark it as a cash outflow; the sign indicates cash-flow direction, not a negative-valued deposit.
- The number of periods, n, appears in an exponent in the future-value annuity equation, so natural logarithms are used to isolate it.
- The demonstrated Excel calculation returns 60 monthly periods, which converts to five years by dividing by 12.
- A practice case changes the inputs to a 7% APR, a $60,000 target, a six-year timeline, and quarterly compounding.
- For an ordinary annuity, each payment is discounted according to when it arrives; a $5,000 payment at the end of year one is discounted once at 10%.
- Later payments are discounted more periods, so the fifth annual payment is discounted five times.
- An annuity-due payment begins at time zero and is not discounted; raising 1.05 to the zero power leaves its value unchanged.
- The example compares a $6 million after-tax lump sum with $20,000 monthly for 30 years, or 360 payments.
- Multiplying $20,000 by 360 gives $7.2 million in nominal payments, but that total does not exist at one point in time.
- To compare the installments fairly with $6 million today, discount each future payment and sum the present values.
- At a 6% annual rate with monthly payments, the first $20,000 installment is discounted once using the monthly rate, and the second is discounted twice.
- The same pattern continues through the 360th payment, which is discounted 360 periods.
- Present value puts the future annuity and today's lump sum on the same time basis, making a direct comparison possible.
- The present value can be written as a sum of payment terms discounted from period 1 through terminal period T.
- A summation sign compresses the long series, but does not by itself make hundreds of terms practical to calculate.
- The annuity formula reduces the series to a manageable expression using payment, periodic rate, and number of periods.
- The lesson presents the annuity present-value formula as payment divided by the rate, minus payment divided by the rate times the growth factor raised to n.
- Factoring the expression can produce a compact equivalent form, though the instructor retains the displayed structure for a later explanation.
- The Excel exercise uses the worksheet's designated cells for the lottery annuity inputs and calculated present value.
- The hypothetical $10 million jackpot offers roughly $6 million as a lump-sum payout or regular annuity payments over about 30 years.
- The example assumes monthly payments of $20,000, yielding 360 installments, while noting real lottery payout schedules can differ.
- Investment returns on a lump sum are uncertain, so the annuity should be evaluated by discounting its cash flows rather than assuming an investment outcome.
- With a 6% APR and 360 monthly payments, the monthly rate is 0.5%, and the annuity's calculated present value is about $3.335 million.
- At that rate, the $6 million lump sum is worth more today than the monthly-payment stream.
- When APR falls to 1%, the annuity's present value rises to about $6.2 million, making the installments preferable under the example's assumptions.
- Lower interest rates reduce the discount applied to future $20,000 installments, increasing their combined present value.
- The example changes the decision from the $6 million lump sum at 6% APR to the annuity at 1% APR.
- The relevant choice depends on the present value of each option under the prevailing rate, not on the annuity's $7.2 million undiscounted total.
- Expected inflation can raise nominal rates because lenders seek compensation for the future loss of purchasing power.
- Higher perceived default risk raises borrowing costs and the return investors demand from risky borrowers.
- The lesson connects real returns to market rates and describes Treasury yields as a benchmark for long-term borrowing costs; it cites yields around or above 5% as context.
- Low inflation and low default risk mean less erosion and risk in future cash flows, which can make a long annuity more attractive in the example.
- Higher inflation, higher risk, and higher discount rates reduce the present value of distant payments, favoring cash today.
- This is a general comparison framework, not a guarantee of investment performance or a substitute for assessing an individual's circumstances.
- The class ends after checking for questions and confirming that students can see the annuity calculation results.
- In response to a quiz question, the instructor says the next assessment is the midterm rather than another quiz.
- The midterm is scheduled through Connect, and students are directed to the course announcement for preparation details.
Transcript excerpt
0:56 Hey, good afternoon everyone. Good afternoon. Happy Thursday. Happy Thursday to everyone. Good afternoon. All right. Uh, I'm glad I could make it. I sent out a um I sent out an uh announcement earlier about the possibility of not making it, but you know, I was I was able to make it um probably later in the afternoon like 6 o'clock finance 300 class. uh that would be probably affected but not uh not this one for now. Um um so um quickly uh
1:51 uh recapping quickly what we did um in the last class. Uh basically uh we found you know future value of annuities using the formula right also annually due right future value of annuity due and also we solve that equation for payment right to nail the exact payment and payment is the same thing as deposit and I've been telling you this over and over uh it's simply >> you switch the turn the table around. I mean if you are are uh
2:38 borrower then you will be making payments right and the present value of all those payments that's the present value of annuities and then if you are the lender right Uh I mean what uh what the borrower pays uh what the borrower makes I mean you know borrower is making a deposit and uh uh again it's also you we have to uh uh make distinction between whether uh this is the future value
3:30 of annuity. I mean in case of future value of annuity it is always deposit. It's a deposit because think about it um graduate school fund uh it's deposit you're making a deposit into the into this account annuality account right but if it is uh present value of annuity problem and it is payment but anyway um
This is the start of the transcript. Save this video free for the whole transcript, searchable and time-coded.
Related notes
Summary, takeaways, and chapters were generated by AI from the video's transcript and may contain errors. The video belongs to its creator, Jeff Hunk.