If you'd like to use the Debt Progress sheet in another Tiller spreadsheet that already has your categorized transaction data in it you can follow these simple steps to get that working. 

Note: the target spreadsheet must have an Accounts sheet. Raw Data, Lauren Gruetman Budget Tracker, and Ben Collins Credit Card Tracker do not have an Accounts sheet.  

  1. Open the target spreadsheet where you want to add the Debt Progress sheet.  
  2. Open the Tools menu and choose "Script Editor" 
  3. Open the File menu in the Script Editor and choose "New > Script File" 
  4. Name the new script file DebtPaydown 
  5. Clear the existing code from the new script file. 
  6. Paste in the contents of this script file (also pasted at the bottom of this help article).
  7. Save the script file by using the "Save" icon or save from the File menu. 
  8. Go back to your Tiller spreadsheet browser tab and copy the URL of your spreadsheet.
  9. Open the Debt Progress sheet from the Reports Master found here.
  10. Right click the "Debt Progress" tab and choose "Copy to > Existing Spreadsheet" 
  11. Paste the URL of your Tiller spreadsheet into the bottom of the window that appears and click "Select"
  12. Navigate back to your Tiller spreadsheet where you just copied in the Debt Progress sheet and double click "Copy of Debt Progress" and rename it "Debt Progress" 
  13. Start filling in the Debt Progress sheet per the instructions here

DebtPaydown.gs Script

var modelSnowball = "snowball";
var modelAvalanche = "avalanche";
var modelRanked = "ranked";

// column indices
var colRow = 0;
var colAccount = 1;
var colInterestRate = 2;
var colMinPayment = 3;
var colRanking = 4;
var colStartingBalance = 5;
var colCurrentBalance = 6;
// --- ADDITIONS ---
var colAnalyze = 7;    
var colEstPayoff = 8;  
var colPaymentAmount = 9;
var colInterestCost = 10;  
var colModelRank = 11;         // for debug only (to test sorting algorithm)

/**
 * Analyzes debt retirement scenario for an array of debts given a monthly payment and payment model.
 *
 * @param {2-D Array} debts Two-dimensional array of debts to process. Columns expected in the following order: "Account Name", "Interest Rate"," Min Monthly Payment", "Rank", "Starting Balance", "Current Balance".
 * @param {Number} payment Monthly payment target amount.
 * @param {String} model Debt paydown model, either "snowball", "avalanche" or "ranked".
 * @param {Boolean} sortOnly If TRUE, return 1-D array of account names sorted by model. If FALSE, perform full analysis, returning 2-D array with columns "Remaining Interest $", "Estimated Paid Off", "Pay This Amount".
 * @return 2D array of analysis
 * @customfunction
 */
function TILLER_DEBTSNOWBALL(debts, payment, model, sortOnly) {
  var sumMinPayment = 0;
   
  // add column to array to preserve original row order
  for(var i = 0; i < debts.length; i++) {
    debts[i].unshift(i);

    // populate colAnalyze column
    if((debts[i][colAccount] == "") ||
       ((debts[i][colInterestRate] == "") && (typeof debts[i][colInterestRate] == 'string')) ||
      ((debts[i][colMinPayment] == "") && (typeof debts[i][colMinPayment] == 'string')))
      debts[i].push(0, "", "", "","");
    else
      debts[i].push(1, "", "", 0, 0);
   
    if((debts[i][colAnalyze]==1) && (debts[i][colCurrentBalance] != 0))
      sumMinPayment += debts[i][colMinPayment];
  }
   
  // sort the debts by priority (first gets the snowball) based on model selection
  if(model == modelSnowball)
    debts.sort(function(x,y) {
      // set aside unpopulated rows
      if(!y[colAnalyze])
        return -1;
      if(!x[colAnalyze])
        return 1;
     
      if((y[colCurrentBalance] == 0) && (x[colCurrentBalance] != 0))
        return 1;
      if((x[colCurrentBalance] == 0) && (y[colCurrentBalance] != 0))
        return -1;

      // if starting balance matches...
      if(x[colStartingBalance] == y[colStartingBalance] ) {
        // secondary criteria is interest rate
        if(x[colInterestRate] == y[colInterestRate] )
          return 0;
        if(x[colInterestRate] < y[colInterestRate] )
          return 1;
        if(x[colInterestRate] > y[colInterestRate] )
          return -1;
      }
      if(x[colStartingBalance] < y[colStartingBalance] )
        return -1;
      if(x[colStartingBalance] > y[colStartingBalance] )
        return 1;
    });
  // default to "avalanche" if no match for model text
  else
    debts.sort(function(x,y) {
      // set aside unpopulated rows
      if(!y[colAnalyze])
        return -1;
      if(!x[colAnalyze])
        return 1;
     
      if((y[colCurrentBalance] == 0) && (x[colCurrentBalance] != 0))
        return 1;
      if((x[colCurrentBalance] == 0) && (y[colCurrentBalance] != 0))
        return -1;

      // if starting interest rate matches...
      if(x[colInterestRate] == y[colInterestRate] ) {
        // secondary criteria is starting balance
        if(x[colStartingBalance] == y[colStartingBalance] )
          return 0;
        if(x[colStartingBalance] < y[colStartingBalance] )
          return 1;
        if(x[colStartingBalance] > y[colStartingBalance] )
          return -1;
      }
      if(x[colInterestRate] < y[colInterestRate] )
        return 1;
      if(x[colInterestRate] > y[colInterestRate] )
        return -1;
    });

  // since the default/else above is avalanche, "ranked" sorting will defer to avalanche when rankings are not populated
  if(model == modelRanked)
    debts.sort(function(x,y) {
      // set aside unpopulated rows
      if(!y[colAnalyze])
        return -1;
      if(!x[colAnalyze])
        return 1;
     
      if((y[colCurrentBalance] == 0) && (x[colCurrentBalance] != 0))
        return 1;
      if((x[colCurrentBalance] == 0) && (y[colCurrentBalance] != 0))
        return -1;

      // demote unranked entries
      if((x[colRanking] == "") && (y[colRanking] == ""))
      return 0;
      if(y[colRanking] == "")
        return -1;
      if(x[colRanking] == "")
        return 1;
     
      // if starting interest rate matches...
      if(x[colRanking] == y[colRanking] )
          return 0;
      if(x[colRanking] < y[colRanking] )
        return -1;
      if(x[colRanking] > y[colRanking] )
        return 1;
    });
 
  // when sortOnly is true, the
  if(sortOnly)
    return debts.map(function(value, index) {
      var row = [value[colAccount], value[colInterestRate], value[colStartingBalance] - value[colCurrentBalance], value[colCurrentBalance], value[colStartingBalance]];
     
      if(value[colAnalyze])
        return row;
      else
        return ["", "", "", "", ""];
    }
                    );
 
  // calculate payment recommendation for current month... step through the loans
  for(var i = 0, iSnowball = payment - sumMinPayment; i < debts.length; i++) {
    // we're done once we hit rows not flagged for analysis
    if(!debts[i][colAnalyze])
      break;
   
    // skip this debt if it's balance is already resolved
    if(debts[i][colCurrentBalance] == 0)
      continue;
   
    // if loan's current balance exceeds snowball + min payment, use the full snowball on it
    if(debts[i][colCurrentBalance] >= debts[i][colMinPayment] + iSnowball) {
      if(debts[i][colMinPayment] + iSnowball > 0)
        debts[i][colPaymentAmount] = debts[i][colMinPayment] + iSnowball;

      iSnowball = 0;
    }
    // otherwise, use some snowball if there is enough snowball and save remainder for another loan
    else if (debts[i][colMinPayment] - debts[i][colCurrentBalance] <= iSnowball){
      iSnowball = iSnowball + (debts[i][colMinPayment] - debts[i][colCurrentBalance]);

      if(debts[i][colCurrentBalance] > 0) {
        debts[i][colPaymentAmount] = debts[i][colCurrentBalance];
      }
    }
    // just drop the rest of the snowball in this debt
    else {
      return (debts[i][colPaymentAmount] + iSnowball);
      if (debts[i][colCurrentBalance] > 0)
        debts[i][colPaymentAmount] = debts[i][colPaymentAmount] + iSnowball;
      iSnowball = 0;
    }
  }
 
  // add column to array to preserve original row order
  for(var i = 0; i < debts.length; i++)
    debts[i][colModelRank] = i;
 
  // track the forecast month with this variable...
  var month = 0;
  var activeDebts;
  var today = new Date();
 
  // run forecast to payoff of all loans
  while(1) {
    activeDebts = false;
    month++;
   
    // update the snowball size
    var iSnowball = payment - sumMinPayment;
   
    // calculate payment recommendation for current month... step through the loans
    for(var i = 0; i < debts.length; i++) {
     
      // we're done once we hit rows not flagged for analysis
      if(!debts[i][colAnalyze])
        break;

      // skip over debts that have been forecast to zero balance
      if(!debts[i][colCurrentBalance])
        continue;

      // flag that this loop updated at least one loan (don't stop yet...)
      activeDebts = true;
     
      // calculate interset cost for the month and add to loan's balance
      var interest = debts[i][colCurrentBalance]*debts[i][colInterestRate]/12;
      debts[i][colInterestCost] += interest;
      debts[i][colCurrentBalance] += interest;
     
      // if loan's current balance exceeds snowball + min payment, use the full snowball on it
      if(debts[i][colCurrentBalance] > debts[i][colMinPayment] + iSnowball) {
        debts[i][colCurrentBalance] -= debts[i][colMinPayment] + iSnowball;
        iSnowball = 0;
      }
      // otherwise, use some snowball and save remainder for another loan
      else {
        iSnowball = iSnowball + debts[i][colMinPayment] - debts[i][colCurrentBalance];
       
        // retire the loan by setting a payoff date, zeroing the balance and reducing the total min payment
        debts[i][colEstPayoff] = new Date(today.getYear(), today.getMonth() + month, 1, 0, 0, 0, 0);
        debts[i][colCurrentBalance] = 0;
        sumMinPayment -= debts[i][colMinPayment];
      }
    }
   
    // quit the while() loop if no active debts
    if(!activeDebts)
      break;
   
    // timeout if debts don't retire in 50 years - (changed to 50 per product owner)
    if(month>12*50) {
      // Throw error message that will populate in the cell.
      throw new Error('The current Monthly Payment will cause you to go over 50 years to pay off your debt. Please try increasing your payment',-1,-1);
    }
  }
 
  // return sort order to original order to repopulate into table
  debts.sort(function(x,y) {
      if(x[colRow] == y[colRow] )
          return 0;
      if(x[colRow] < y[colRow] )
        return -1;
      if(x[colRow] > y[colRow] )
        return 1;
    });
 
  return debts.map(function(value, index) { var row = [value[colInterestCost], value[colEstPayoff], value[colPaymentAmount]]; return row; });
}

function TILLER_DEBTSNOWBALLCOMPARE(debts, payment, model) {
  var data = TILLER_DEBTSNOWBALL(debts, payment, model, false);
  var interestTotal = 0;
  var dateMax = 0;
 
 
  // add column to array to preserve original row order
  for(var i = 0; i < debts.length; i++) {
    if(debts[i][colAnalyze])
      interestTotal += debts[i][colInterestCost];
   
    if(debts[i][colEstPayoff] > dateMax)
      dateMax = debts[i][colEstPayoff];
  }
 
  return [interestTotal, dateMax];
}

Did this answer your question?