Debt Consolidation: A Sensitivity Analysis
by Kenneth P. Moon, Ph.D., and Christine A. McClatchey, Ph.D.

Recent research in the area of debt consolidation and refinancing demonstrates the need to evaluate all relevant cash flows, on a present value basis, when considering the decision to restructure one's debt obligations. Marcus (1996) examines the decision to refinance an existing mortgage with a new lower-rate mortgage by providing a breakeven analysis, which determines how long it takes to recover the upfront costs of refinancing. He aptly demonstrates that the analysis is complicated by the fact that one must consider the balances of the two mortgages at the breakeven point, as well as the present value of principal payments, interest payments, and refinancing costs. This framework highlights the need for an iterative process to solve the problem. In a similar vein, Hoover (2003) provides an examination of the payback, or breakeven, method and develops a generalized mathematical framework for analyzing the refinancing decision. The key result of Hoover's paper is that all relevant variables need to be considered, such as payments, tax implications, and the appropriate opportunity cost of funds. The primary shortcoming of these articles for the specific question at hand is that they do not necessarily provide a basis for including other existing debt obligations in the refinance decision. In other words, these articles focus on simply refinancing one's existing mortgage and determining the optimal time it takes to recover the cost of refinancing a single debt obligation.

The consolidation of several debt obligations complicates the analysis because it is likely that some of them do not follow a simple annuity amortization schedule. Additionally, the client's obligations may differ in terms of their maturity, interest rate, and tax deductibility. Hence, in these circumstances, a simple breakeven analysis is not warranted; rather, one needs to examine the total benefit derived from debt consolidation. Basu (2003) provides an excellent framework for examining the decision to restructure a client's multiple debts. The framework developed in his article essentially computes the total future benefit from all cash flows associated with the restructuring decision. Basu correctly demonstrates that all relevant cash flows need to be considered; it is not sufficient to simply compare the current monthly savings from a consolidation.

We clarify and extend Basu's research to include a sensitivity analysis on key input values using the data table function in Excel. We apply the analysis to three clients who differ in their existing debt obligations. The results clearly show that the optimal structure, or terms of the new loan, is dependent on each client's specific situation, and not merely on their common objective to reduce their monthly obligations. Alternatively, we find that some loan terms have a significant impact on the consolidation benefits for one client, but only marginal effects for another.

Clients' Existing Debt Structures

The existing debt structures for our hypothetical clients are provided in Table 1. All three clients have two car loans, which will be repaid in 36 and 18 months, and an education loan that will be repaid in 60 months. We assume interest expense on the education loan is tax-deductible.

Moon Table 1

Jo Ann Smith, hereafter referred to as Client A, also owes $10,000 and $20,000 on two credit cards. She pays only the minimum interest charge each month, so she must make a $30,000 balloon payment under her existing debt structure in order to fully retire her debt. Scott Holland (Client B) has similar credit card balances, but he pays more than the minimum interest charge each month; in other words, his credit card debt is quasi-amortized. In contrast, Debbie and Bill McClain (Client C) have no credit card obligations. The final row for each client provides the sum of their outstanding debt (the amount to be consolidated) and their current monthly payments (denoted P0).

We assume each client is considering consolidating their existing debts with a second mortgage on their homes.¹ The consolidated loan carries a 7 percent interest rate (compounded monthly) and calls for monthly payments over a ten-year period. The terms of the new consolidated loan, including its required monthly payment (PC), are summarized in Table 2.

Moon Table 2

On the surface, each client might consider the new loan because it offers a lower current monthly payment; that is, P0 – PC is (initially) positive. Further, one might (inappropriately) conclude that Client B stands to benefit the most from consolidation because he realizes the largest current monthly savings ($1,101.26) compared with Clients A and B (current monthly savings of $801.26 and $749.58, respectively). But as Basu (2003) demonstrates, this comparison may be misleading; the planner needs to stress to the client that all relevant cash flows must be considered, and the decision should be based on the total net benefit, rather than on the initial monthly savings. Specifically, the correct analysis considers the changes in all cash flows on a present-value basis. Tables 3, 4, and 5 summarize our results for Clients A, B, and C, respectively.
The first column reflects the monthly payment differential or P0 – PC . While initially positive for all clients, the differential turns negative in month 61 as both car loans and the education loan are paid in full in month 60 under the original debt structure (causing P0 to fall). Column 2 reflects the cumulative after-tax interest income earned from reinvesting each month's payment differential. We initially assume each client can earn 4 percent on reinvested cash flows, and faces a 30 percent marginal tax rate; hence, each client's after-tax reinvestment rate is equal to 2.8 percent. Columns 3 and 4 show the monthly tax savings, and cumulative interest earned on that savings, arising from the deductibility of interest on the new consolidated loan. The monthly tax savings is computed by multiplying that month's interest payment by the client's tax rate (INT*T). Negating part of this benefit are similar tax-induced cash flows that are lost when the education loan is eliminated under consolidation (columns 5 and 6). Note, however, that for all clients, the added tax-related benefits of the consolidated loan exceed those lost on the education loan because the consolidated loan carries a larger balance. The next column accommodates the balloon payment on the credit cards that would have been required under the original loan terms. Under consolidation, the credit card debt is amortized over the ten-year period, eliminating the large cash outflow at the end of the term (this makes it a benefit, or cash inflow, under the consolidation analysis). The final column, "Total Consolidation Cash Flows," sums all of the cash-flow differentials that arise from the decision to consolidate. We discount these cash flows at the client's opportunity cost of funds, or his or her after-tax reinvestment rate (Hoover 2003). The resulting net present value (NPV) represents the "benefits to consolidation" in present value terms. We find that that Client A receives the greatest net benefit (+$35,216) followed by Client B (+$15,104), and finally Client C (+$2,548).

Sensitivity Analysis

The decision to consolidate clearly depends on each client's unique circumstances. While our model accommodates all relevant cash flows, and provides for a yes/no decision to be made given the proposed loan's terms, its real value extends much further when combined with sensitivity analysis. Fortunately, Excel provides a built-in function that assists the user in this manner. While any spreadsheet generally allows changes to be transferred throughout the spreadsheet (assuming the model was developed via linked formulae), the data table feature provides for considerably reduced computations. Essentially, the data table feature allows the user to identify a range of values for a specific variable of interest, and automatically recalculates a specified output value (in this case NPV), so the user does not have to examine each input value one at a time.²

Moon Table 3

Moon Table 4

Moon Table 5

The value of performing sensitivity analysis is twofold. First, it allows the user to vary the terms of the proposed consolidated loan, such that the best loan terms can be negotiated; it is unlikely that the initial loan terms, or inputs, are those that maximize the client's benefits of consolidation. Second, sensitivity analysis allows the user to identify which assumptions or loan terms have the greatest impact on the decision to consolidate. For example, it may be that the difference in NPV between a 60-month and 72-month consolidated loan is marginal. In this case, the client's personal preferences may dictate which terms to accept. We perform four sensitivity analyses via the use of a data table on the loan consolidation decision for the following key input variables:

  1. The consolidated loan's interest rate
  2. The client's marginal tax rate
  3. The assumed reinvestment rate
  4. The consolidated loan's maturity

The results are summarized in Table 6 and illustrated graphically in Figures 1–4.

Moon Table 6

NPV Sensitivity to the Consolidated Loan Rate

Figure 1 shows the sensitivity of NPV to changes in the consolidated loan rate. We allowed the consolidated loan rate to vary from 5 to 10 percent. Recall that NPV measures the benefits of consolidation, in present value terms; hence we obtain a negative relationship: the higher the consolidated loan rate, the lower the consolidation benefits (lower NPV). We find, at any loan rate, the benefits for Client A are greater than the benefits for Client B, and both are greater than the benefits for Client C. This is because Client A had the most outstanding debt at the highest interest rates (credit card balances); hence the opportunity to refinance at a lower interest rate is most beneficial to her. Figure 1 also illustrates the usefulness of the analyses in identifying the breakeven loan rate. For example, the NPV of the consolidation decision turns negative for Client C around 8.5 percent. Thus, Clients A and B will find consolidation beneficial at most reasonable interest rates; however, Client C must be able to obtain a consolidated loan at a rate less than 8.5 percent for the decision to be financially advantageous.

Moon Figure 1

NPV Sensitivity to the Client's Marginal Tax Rate

Figure 2 illustrates the sensitivity of the consolidated loan's NPV to changes in the client's marginal tax rate. We allowed the tax rate to vary from 10 to 40 percent, in 5 percent increments. Unlike the prior analysis, a change in the client's tax rate is significantly more complex because it affects two distinct aspects of the analysis: (1) cash flows, since they are considered after taxes, and (2) the discount rate used to compute present values since it is an after-tax rate—a higher tax rate leads to a lower discount rate.

Moon Figure 2

First consider the impact of an increasing tax rate on Client A's decision to consolidate. There is no impact on the monthly payment differential (P0 – PC), so the only effect is that of a lower discount rate. Recall, however, that the sign of this cash flow changes during the term of the new loan. That is, Client A's payment differential is positive for months 1–60 because the consolidated loan payment is less than the sum of the original monthly payments (P0 > PC). But Client A has repaid both auto loans and the education loan by month 60; thus the payment differential turns negative in month 61 (P0 < PC) and is negative for the last 60 months of the new loan term. A lower discount rate (induced by a higher tax rate) increases the present value of all cash flows. Furthermore, this effect is larger for cash flows further out in time. Although the sum of the positive payment differentials for months 1–60 are roughly equivalent to the sum of the negative payment differentials for months 61–120, the impact is greater on the later negative cash flows. The combined effect is that the present value of the payment differential falls as the client's tax rate increases. The impact on the credit card balloon payment is similar. The cash flow itself is unchanged; however, its present value increases for higher tax rates (lower discount rates). Because the consolidated loan eliminated the balloon payment by essentially amortizing it, it is a positive cash flow in the consolidated loan. Overall, an increase in the tax rate causes this component to increase the NPV or attractiveness of the consolidation decision.

The impact on the consolidated loan and education loan cash flows are the same, although opposite in sign. That is, the consolidated loan creates a new tax shelter for the client, at the expense of losing the education loan's existing tax shelter. A change in the tax rate affects both the cash flow and the discount rate for each loan; further, there are actually two cash-flow effects to consider. The tax shelter itself is equal to the product of the interest expense times the tax rate (INT*T). Thus, a higher tax rate increases the dollar amount of the tax shelter. This tax savings is then assumed to be reinvested each month at the after-tax reinvestment rate, which declines as the client's tax rate increases. But because the dollar amount of the monthly tax savings gets larger, total interest income earned (or lost, in the case of the education loan) actually increases, despite a lower reinvestment rate. Discount rate effects are similar to those discussed above—a higher tax rate lowers the discount rate, which increases present values. For the consolidated loan, the overall impact is positive—the client receives larger cash flows (tax shelter and interest income) discounted at a lower interest rate, both of which increase the NPV of consolidation. For the education loan, the overall impact is negative—the client loses larger cash flows discounted at a lower interest rate, both of which decrease the NPV of consolidation.

The end result for Client A is that the value of the payment differential and the interest income earned on it fall, but are roughly offset by the increase in value coming from elimination of the credit card balloon payment. Further, because the consolidated loan is much larger in dollar terms than the education loan, losses on the latter are more than offset by gains on the former at higher tax rates. Taken together, we see that the NPV of the consolidation decision increases for Client A as their marginal tax rate increases.

As seen in Table 6, Clients B and C are affected in an overall similar manner, albeit to a much lesser degree. Client B differs from Client A only in terms of their monthly payment differential and credit card balloon payment; all cash flows (in present value terms) related to the new loan and replaced education loan are identical. Client B repays his credit cards during the term of the new loan by making extra principal payments. Relative to Client A, then, Client B has a greater payment differential up front (that is, P0 – PC is larger for Client B); however, it is also more negative at the end. Furthermore, Client B has no credit card balloon payment that is eliminated in the consolidation because his credit card balances are zero at the end of 120 months.

A higher tax rate also increases the NPV of consolidation for Client C, albeit to a much lesser degree, for two reasons. First, Client C has no high-rate credit card debt to refinance. Client C borrows only $43,000 on the consolidated loan (compared with $73,000 for Clients A and B). Second, the blended interest rate on the car and education loans is only marginally higher than that offered by the consolidated loan. In effect, Client C's situation is much more that of a substitution—they are replacing one tax-sheltered loan for another, at a marginally lower rate of interest.

Overall, we find an increase in the client's tax rate increases the benefits of consolidation. The key drivers of this benefit are (1) substitution of a lower interest rate on all debt and (2) the additional tax deductions on the consolidated loan. In addition, Client A was also rewarded post-consolidation with the removal of the lump-sum credit card repayment at the end of the term. Because the payment was quite large, and occurred late in the timeline, the effects of a lower discount rate substantially increased the present value of this savings.

NPV Sensitivity to the Client's Reinvestment Rate

We next examine the sensitivity of the consolidated loan's NPV to changes in the assumed reinvestment rate. The results are provided in Figure 3. We examine reinvestment rates from 0 percent to 7 percent, in 1 percent increments. Again, the analysis is complicated in that changes in the reinvestment rate affect both cash flows received (or lost) and the discount rate. For all clients we find that an increase in the reinvestment rate increases NPV, or total benefits to consolidation. The magnitude of the increase is, however, quite different for each client (see Table 6). As illustrated in Figure 3, the slope of the NPV profile is much steeper for Clients B and C than it is for Client A, suggesting the reinvestment rate is more critical for these clients.

Moon Figure 3

For all clients, an increase in the reinvestment rate causes the present value of the monthly payment differential to rise. Recall that, for all clients, the monthly payment differential (P0 – PC) is positive in months 1–60 and negative in months 61–120. While a higher discount rate reduces the present values of the positive cash flows, it lowers the present values of the negative cash flows more because they are further out in time. For all clients, the present value of the interest earned from the payment differential increases with an increase in the reinvestment rate. Although these are also discounted a higher interest rate, the combined effect is positive. The credit card balloon payment affects only Client A; its direction is as expected—a higher rate reduces the present value of the payment. This cash flow works to offset the previously discussed gains in the payment differential and is the reason the slope of the NPV graph is much flatter for Client A than it is for Clients B or C.

Once again, tax factors related to the consolidated loan and the education loan partially offset one another. That is, a portion of the tax benefits gained from the consolidated loan are offset by the tax benefits lost from the education loan. The reinvestment rate has no impact on the consolidated loan's monthly tax savings, but it does increase the interest earned as the savings accumulates each month. However, all of these cash flows are discounted at the after-tax reinvestment rate; hence, their present values fall. The combined effects (the tax savings and the reinvestment interest income in present value terms) actually increase with increases with the reinvestment rate for each client. The opposite is true for the education loan; in present value terms, the lost tax shelter becomes larger as the reinvestment rate increases. The combined effect of loans, however, increases with an increase in the reinvestment rate because the consolidated loan is larger than the education loan.

NPV Sensitivity to the Consolidated Loan's Maturity

The impact of loan maturity on the consolidation's NPV is illustrated in Figure 4. We examined loan maturities of 60–120 months, in 12-month increments.³ None of the costs/benefits associated with the education loan vary with the maturity of the consolidated loan; hence they need not be considered. Furthermore, all clients benefit from a longer-term loan for tax-related benefits; that is, the present value of the tax shelters and reinvestment income both increase with maturity.

Moon Figure 4

Under the original payment schedule, all clients paid off the car loans in months 18 and 36, and the education loan in month 60. Client A also had credit card debt, but she paid only the required minimum interest charge under the original terms. Because of this, Client A's original monthly payments (P0) were relatively low. At some future point, however, Client A must repay the credit card balloon payment under her original terms. The consolidated loan amortizes all debt (credit cards, auto, and education) evenly over the new loan's maturity; the shorter the term of the new loan, the higher the required monthly payment. At very short loan terms (60 months, for example) the monthly payment differential is large and negative for Client A. As the loan's maturity increases, the differential turns positive and grows larger with maturity. This, however, is offset by the cash flow associated with elimination of the credit card balloon payment. That is, the shorter the consolidated loan term, the larger this benefit is in present value terms (because it is discounted to the present for future years). Overall, the combined impact of (1) the payment differential, (2) interest earned on the payment differential, and (3) the credit card balloon payment is positive and increases with maturity for Client A.

In contrast, Client C had no prior credit card debt; all of their existing obligations were amortized. Client C is really "refinancing" their existing debt obligations at a lower, tax-sheltered, rate of interest. At all maturities (60–120 months), the monthly payment differential is initially positive because (1) the consolidated loan has an interest rate lower than any of the existing debt and (2) the consolidated loan amortizes all three loans over 60 months, rather than their current shorter terms. After month 36, however, the payment differential turns negative because both car loans are paid off, thus lowering P0 substantially. But as maturity increases, the interest earned on the payment differential increases, since a larger amount is deposited earlier; this partially offsets the decline in the present value of the payment differential. Overall, the combined payment differential and interest-related effects decline as the new loan's maturity increases. But this is all offset by an increase in the tax savings. When taken together, the NPV of the consolidation decision increases with an increase in maturity, although the savings is small. This is seen graphically in Figure 4 whereby the NPV profile for Client C is relatively flat curve throughout the maturity spectrum.

In terms of loan maturity, Client B can be viewed as a "hybrid" of Clients A and C. Client B's car and education loans are repaid in a similar manner to both Clients A and B. Client B has credit card debt outstanding in earlier years, but the first credit card is fully repaid in month 47 and the second credit card is fully repaid in month 79. As a result, Client B behaves similarly to Client A for consolidated loan maturities between 60 and 79 months (both have a credit card balloon payment for these maturities), and like Client C for consolidated loan maturities greater than 79 months (because the credit cards are paid off and there is no balloon payment). In fact, close examination of Figure 4 shows that the slope of the NPV profile is steeper for loan maturities in the 60- to 72-month range, similar to Client A, and flatter in the 84- to 120-month range, similar to Client C. Overall, the benefits to consolidation increase as the consolidated loan's maturity increases, although the added benefits are marginal in present value terms (see Table 6).


The decision to refinance or consolidate existing debts into one consolidated loan is not as easy as many believe. Many consumers fall prey to loan consolidation advertisements by simply comparing their current monthly obligations with the monthly obligation of the consolidated loan (that is, the initial difference between P0 and PC). Recent research and general financial theory correctly suggest that the consumer needs to consider all relevant cash flows associated with the decision. This article expands current research by providing a simple framework to perform a loan consolidation sensitivity analysis relative to key input assumptions using Excel's data table function. The end result is that optimal debt restructuring is specific to the individual consumer's debt situation. Consumers with seemingly similar existing debt obligations do not acquire equal benefits from the same loan consolidation structure. The best loan structure is a function of the existing obligations, as well as their general repayment habits.


  1. There are essentially three avenues a client may consider when attempting to consolidate current debt obligations. The first method entails the client refinancing their existing mortgage and "cashing-out" equity to eliminate their current debts. The second method involves the use of a home equity line of credit (HELOC). The final method employs the use of a second mortgage in an amount equal to the current debt obligations. We examine the use of a second mortgage, although our results are easily generalized to any of the refinancing choices.
  2. See the appendix for a more detailed explanation and example of the data table feature in Excel.
  3. NPV was calculated for consolidated loan's period. That is, when a 60-month loan was assumed, we computed the NPV of the consolidation benefits for a 60-month period, not 120 months (the base case maturity).


Basu, Somnath. 2003. "A Simplified and Accurate Approach to Debt Consolidation Analysis." Journal of Financial Planning: 72–77.

Hoover, Gary L. 2003. "The Mortgage Refinance Decision: An Equation-Based Model." Financial Services Review. 319–337.

Marcus, David R. 1996. "The Mortgage Refinancing Decision: A Break-Even Approach." The CPA Journal February: 69–71.


Kenneth P. Moon, Ph.D., is an assistant professor of finance at the McCoy College of Business at Texas State University in San Marcos, Texas, and has written several articles in the areas of financial planning and investments.

Christine A. McClatchey, Ph.D., is an associate professor of finance at the Monfort College of Business, University of Northern Colorado, in Greeley, Colorado.

Close window