Developers Guide to Lending - Full Code Example

index.js

/**
 * Developers Guide to Lending in Xero
 * Author:      Jordan Walsh
 * Date:        4 May 2017
 * Description: 
 * This script creates the necessary components to write back a loan into Xero. 
 * 
 * This should only be run on a Demo company and NEVER used in a live environment.
 * 
 * This code is made available at your own risk.  
 * 
 * Do your research and make sure it's right for you before running it.
 * 
 * Any questions, tweet @jordwalsh or @XeroAPI
 */

var xero = require('xero-node');
var fs = require('fs');
var dateFormat = require('dateformat');
var config = require('/Path/To/Your/config.json');

//Private key can either be a path or a String so check both variables and make sure the path has been parsed.
if (config.privateKeyPath && !config.privateKey)
    config.privateKey = fs.readFileSync(config.privateKeyPath);

var privateApp = new xero.PrivateApplication(config);

/**
 * Method
 * 0. Customer must select a bank account (we can look it up)
 * 1. Create a new contact for the loan account
 * 2. Create a new account for the loan interest
 * 3. Create a new account for the loan liability
 * 4. Create a 'RECEIVE' bank transaction to reconcile the dispursement amount
 * 5. Create 'SPEND' bank transactions (or accpay invoices) to reconcile the payments
 * 6. Attach the loan agreement document (PDF) to the contact
 */

//Set up loan parameters
var loanAmount = 75000;
var interestPct = 4.6;
var payments = 12;
var loanID = Number(Math.random() * 10000).toFixed(0);

//Use Invoices or Bank Transactions
var useInvoices = false;

//Set up some other placeholders
var bankAccountID = "";
var interestAccountID = "";
var loanAccountID = "";
var contactID = "";

// 0. Get the bank account

//filter accounts that are type Bank
var filter = 'Type == "BANK" and Status == "ACTIVE"';

privateApp.core.accounts.getAccounts({ where: filter })
    .then(function(accounts) {
        //Get the ID of the bank account
        bankAccountID = accounts[0].AccountID;
    })
    .then(function() {
        //Create a contact
        var sampleContact = {
            Name: 'Example Loans [' + loanID + ']'
        };

        var contactObj = privateApp.core.contacts.newContact(sampleContact);

        return contactObj.save();
    })
    .then(function(contacts) {
        //Save the contact ID
        contactID = contacts.entities[0].ContactID;
    })
    .then(function() {
        //Create the account for the loan interest
        var data = {
            Code: 'LOANE' + loanID,
            Name: 'Loan Interest [' + loanID + ']',
            Type: 'EXPENSE',
            TaxType: 'EXEMPTEXPENSES'
        };

        var accountObj = privateApp.core.accounts.newAccount(data);

        return accountObj.save();
    })
    .then(function(accounts) {
        interestAccountID = accounts.entities[0].AccountID;
    })
    .then(function() {
        //Create the account for the loan interest

        var acctType = 'TERMLIAB';

        //If this will be paid off in the current year it should be a current liability
        if (payments < 12) {
            acctType = 'CURRLIAB';
        }

        var data = {
            Code: 'LOANC' + loanID,
            Name: 'Loan from [' + loanID + ']',
            Type: 'TERMLIAB',
            TaxType: 'BASEXCLUDED'
        };

        var accountObj = privateApp.core.accounts.newAccount(data);

        return accountObj.save();
    })
    .then(function(accounts) {
        loanAccountID = accounts.entities[0].AccountID;
    })
    .then(function() {
        var receiptDate = new Date();

        //Create the payments in the past so it reconciles well
        receiptDate.setYear(receiptDate.getYear() + 1896);

        //Create the bank transaction 'RECEIVE' event
        var sampleData = {
            Type: "RECEIVE",
            Contact: {
                ContactID: contactID
            },
            BankAccount: {
                AccountID: bankAccountID
            },
            LineItems: [{
                Description: 'Loan Received from [' + loanID + ']',
                UnitAmount: loanAmount,
                Quantity: 1,
                AccountCode: 'LOANC' + loanID
            }],
            Reference: 'Loan Dispursal from [' + loanID + ']',
            Date: dateFormat(receiptDate, "yyyy-mm-dd")
        }

        var transaction = privateApp.core.bankTransactions.newBankTransaction(sampleData);

        return transaction.save();
    })
    .then(function() {
        if (useInvoices) {
            return;
        }
        //Create the spend money transactions (payments)
        var paymentCurrentMonth = new Date();

        //Create the payments in the past so it reconciles well
        paymentCurrentMonth.setYear(paymentCurrentMonth.getYear() + 1896);

        //Payment interest calculations..
        var dailyRate = interestPct / 365;
        var P = loanAmount; //principal / initial amount borrowed
        var I = parseFloat(interestPct) / 100 / 12; //monthly interest rate
        var N = payments; //number of payments months

        var monthlyPayment = calculateMonthlyPayment(P, N, I);

        var paymentTemplate = {
            Type: "SPEND",
            Contact: {
                ContactID: contactID
            },
            BankAccount: {
                AccountID: bankAccountID
            },
            LineItems: [],
            Reference: 'LOAN Loan Id ' + loanID + '/'
        }

        var importDate = dateFormat(paymentCurrentMonth, "dd/mm/yyyy");

        //Set up the CSV
        console.log("Date,Amount,Payee,Reference");
        console.log(importDate + "," + loanAmount + ",Example Loans,Loan Dispursal from LOAN [" + loanID + "]")

        //Start the payments from next month
        paymentCurrentMonth.setMonth(paymentCurrentMonth.getMonth() + 1);

        var paymentCount = 0;

        while (loanAmount > 0) {
            var interest = ((dailyRate * paymentCurrentMonth.monthDays()) * (loanAmount)) / 100;
            var principal = monthlyPayment - interest;
            var originalReference = paymentTemplate.Reference;

            // console.log("LoanAmount:", loanAmount);
            // console.log("Principal:", principal);

            if (loanAmount - principal < 20) {
                principal = loanAmount;
                monthlyPayment = principal + interest;
                // console.log("Updated Principal:", principal);
                // console.log("Updated monthlyPayment:", monthlyPayment);
            }

            //Add the line items
            paymentTemplate.LineItems.push({
                Description: 'Principal repayment',
                UnitAmount: principal,
                Quantity: 1,
                AccountCode: 'LOANC' + loanID
            });
            paymentTemplate.LineItems.push({
                Description: 'Interest repayment',
                UnitAmount: interest,
                Quantity: 1,
                AccountCode: 'LOANE' + loanID
            });

            //Update the payment reference
            paymentTemplate.Reference += ++paymentCount;

            //Add the new date
            paymentTemplate.Date = dateFormat(paymentCurrentMonth, "yyyy-mm-dd");

            //create the payment   
            var payment = privateApp.core.bankTransactions.newBankTransaction(paymentTemplate);

            payment.save();

            //console.log the CSV file
            var importDate = dateFormat(paymentCurrentMonth, "dd/mm/yyyy");
            console.log(importDate + "," + (monthlyPayment * -1) + ",\"Example Loans payment: " + paymentCount + "\",\"" + paymentTemplate.Reference + "\"");

            paymentTemplate.LineItems = [];
            paymentTemplate.Reference = originalReference;
            loanAmount -= principal;
            paymentCurrentMonth.setMonth(paymentCurrentMonth.getMonth() + 1);
        }
    })
    .then(function(bankTransactions) {
        console.log("Created Scheduled Payments for loan: " + loanID);
    })
    .then(function() {
        if (!useInvoices) {
            return;
        }
        //Create the accounts payable invoices
        var paymentCurrentMonth = new Date();

        //Create the payments in the past so it reconciles well
        paymentCurrentMonth.setYear(paymentCurrentMonth.getYear() + 1896);

        //Payment interest calculations..
        var dailyRate = interestPct / 365;
        var P = loanAmount; //principal / initial amount borrowed
        var I = parseFloat(interestPct) / 100 / 12; //monthly interest rate
        var N = payments; //number of payments months

        var monthlyPayment = calculateMonthlyPayment(P, N, I);

        var invoiceTemplate = {
            Type: "ACCPAY",
            Status: "AUTHORISED",
            Contact: {
                ContactID: contactID
            },
            LineItems: [],
            Reference: 'Loan Id ' + loanID + '/'
        }

        var importDate = dateFormat(paymentCurrentMonth, "dd/mm/yyyy");

        //Set up the CSV
        console.log("Date,Amount,Payee,Reference");
        console.log(importDate + "," + loanAmount + ",\"Example Loans\",\"Loan Dispursal from [" + loanID + "]\"");

        //Start the payments from next month
        paymentCurrentMonth.setMonth(paymentCurrentMonth.getMonth() + 1);

        var paymentCount = 0;

        while (loanAmount > 0) {
            var interest = ((dailyRate * paymentCurrentMonth.monthDays()) * (loanAmount)) / 100;
            var principal = monthlyPayment - interest;
            var originalReference = invoiceTemplate.Reference;

            // console.log("LoanAmount:", loanAmount);
            // console.log("Principal:", principal);

            if (loanAmount - principal < 20) {
                principal = loanAmount;
                monthlyPayment = principal + interest;
                // console.log("Updated Principal:", principal);
                // console.log("Updated monthlyPayment:", monthlyPayment);
            }

            //Add the line items
            invoiceTemplate.LineItems.push({
                Description: 'Principal repayment',
                UnitAmount: principal.toFixed(2),
                Quantity: 1,
                AccountCode: 'LOANC' + loanID
            });
            invoiceTemplate.LineItems.push({
                Description: 'Interest repayment',
                UnitAmount: interest.toFixed(2),
                Quantity: 1,
                AccountCode: 'LOANE' + loanID
            });

            //Update the payment reference
            invoiceTemplate.Reference += ++paymentCount;

            //Add the new date
            invoiceTemplate.Date = dateFormat(paymentCurrentMonth, "yyyy-mm-dd");
            invoiceTemplate.DueDate = dateFormat(paymentCurrentMonth, "yyyy-mm-dd");

            //create the payment   
            var invoice = privateApp.core.invoices.newInvoice(invoiceTemplate);

            invoice.save();

            //console.log the CSV file
            var importDate = dateFormat(paymentCurrentMonth, "dd/mm/yyyy");
            console.log(importDate + "," + (monthlyPayment.toFixed(2) * -1) + ",\"Example Loans payment: " + paymentCount + "\",\"" + invoiceTemplate.Reference + "\"");

            invoiceTemplate.LineItems = [];
            invoiceTemplate.Reference = originalReference;
            loanAmount -= principal;
            paymentCurrentMonth.setMonth(paymentCurrentMonth.getMonth() + 1);
        }
    })
    .then(function(invoices) {
        console.log("Created forward dated invoices for loan: " + loanID);
    })
    .then(function() {
        //Attach the loan agreement to the Contact

        var data = {
            FileName: "some-attachment.pdf",
            MimeType: "application/pdf"
        };

        var filePath = __dirname + "/some-attachment.pdf";
        var fileReadStream = fs.createReadStream(filePath);
        var attachmentObj = privateApp.core.attachments.newAttachment(data);

        return attachmentObj.save('Contacts/' + contactID, fileReadStream, true);

    })
    .then(function(attachments) {
        console.log("Added attachment: " + attachments.entities[0].FileName);
    })
    .catch(function(err) {
        console.log("Error: ", err);
    })


function calculateMonthlyPayment(p, n, i) {
    return p * i * (Math.pow(1 + i, n)) / (Math.pow(1 + i, n) - 1);
}

//Add a new function to the javascript date object
Date.prototype.monthDays = function() {
    var d = new Date(this.getFullYear(), this.getMonth() + 1, 0);
    return d.getDate();
}